MS Access – VBA – Change the Application Printer to a Given Printer Name

Once again trying to help someone in a thread who needed to set the Application Printer to a specific printer name. Now the issue being, and if you are here you are already ware of the problem, is that when you try and set the application printer it is expecting the printer No, not its name! So what is one to do? Below is a simple function which basically loops through the printer collection and checks for the printer name being called, and once it find that one, it sets the application printer to that printer.

'---------------------------------------------------------------------------------------
' Procedure : SwicthAppPrinter
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Change the application printer
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPinterName   : Name of the printer to set the application to use by default
'
' Usage:
' ~~~~~~
' Call SwicthAppPrinter("Snagit 10")
' Call SwicthAppPrinter("Brother MFC-240C")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Oct-23                 Initial Release
'---------------------------------------------------------------------------------------
Function SwicthAppPrinter(sPinterName As String)
On Error GoTo Error_Handler
    Dim prtAvailPrinters As Printer
 
    For Each prtAvailPrinters In Application.Printers
        If prtAvailPrinters.DeviceName = sPinterName Then
            Set Application.Printer = prtAvailPrinters
            Exit For
        End If
    Next prtAvailPrinters
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: SwicthAppPrinter" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

It is a simple workaround to Microsoft not having given us any direct way to set the application printer to a given printer name, which let’s be serious, it the way we need to work! Who knows what No a printer is from one system to another. Setting a printer based on an arbitrary No is moronic! Yes I said it!!!

I should also specify that it would probably be a good idea to initially capture the active default printer and set it back once you are done. As such, you can determine the active default printer with a couple simple lines of code:

Dim sDefaultPrnt As Printer
Set sDefaultPrnt = Application.Printer

Other Resources:

If you are looking for more printer functions, pere_de_chipstick was kind enough to share some of his code in the UtterAccess Code Archive, see:
Select Printers, Configuration, Paper Sizes And Paper Bins For Reports, Any Version

VBA-WMI-Determine If a Process or Program Is Running Or Not

Here’s a simple technique (pulling it out of archive to help answer a thread in a discussion forum) to verify whether or not a program (actually a process) is currently running on a computer, local or remote (if the permissions have been establish to validate such information remotely), using a simple WMI query.

'---------------------------------------------------------------------------------------
' Procedure : IsExeRunning
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a process or Program is running or not
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sExeName  : Name of the process to look for
' sComputer : Name of the computer or IP to check on, ommit for the local computer
'
' Usage:
' ~~~~~~
' IsExeRunning("firefox.exe")
' IsExeRunning("outlook.exe")
' IsExeRunning("msaccess.exe", "HomePC01")
' IsExeRunning("msaccess.exe", "192.168.100.12")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-03             Initial Release
'---------------------------------------------------------------------------------------
Public Function IsExeRunning(sExeName As String, Optional sComputer As String = ".") As Boolean
On Error GoTo Error_Handler
    Dim objProcesses    As Object
 
    Set objProcesses = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2").ExecQuery("SELECT * FROM Win32_Process WHERE Name = '" & sExeName & "'")
    If objProcesses.Count <> 0 Then IsExeRunning = True
 
Error_Handler_Exit:
    On Error Resume Next
    Set objProcesses = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: IsExeRunning" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

There are some other great resources on the subject of WMI to further develop such code. I good starting point is MSDN, see:

WMI Tasks: Processes

MS Access – List Back-End Data Sources

Ever taken over a database that was a complete mess and quickly wanted to determine what Back-End data sources were involved? Below is a simple function to enumerate the Back-End source in a given database.

Dim collTables As New Collection
 
Public Function ListBESources() As String
On Error Resume Next
    Dim db              As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim strCon          As String
    Dim strBackEnd      As String
 
    Set db = CurrentDb
 
    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
        'Ensure the table is a linked table.
        If Left$(tdf.Connect, 10) = ";DATABASE=" Then
            'Get the path/filename of the linked back-end
            strBackEnd = Mid(tdf.Connect, 11)
            'Ensure we have a valid string to add to our collection
            If Len(strBackEnd & "") > 0 Then
                collTables.Add Item:=strBackEnd, Key:=strBackEnd
            End If
        End If
    Next tdf
 
On Error GoTo 0
    Debug.Print collTables.Count & " Data Source(s) found:"
    For Each BE In collTables
        Debug.Print BE
    Next BE
End Function

Please note that the above was very quickly put together to meet an immediate need and has not been fully tested and proper error handling and variable cleanup also needs to be implemented. That said, the basic principle is sound!

MS Access – Run a Macro in an External Database

Here is another common question: “How can I run/execute a Macro in another external database?”

Well, Like with most MS Access related things, there are several possible solutions.

Copy them into the Current Database

In some cases, the solution is as simple as copying the macro into the current database and creating linked tables to the data required by the Macro. This way you can actually run the macro locally. No more need to execute anything remotely.

Use the /x Command Line Switch

Another solution is to use the Shell command in conjunction with the /x command line switch to run a specified macro.  The problem with this approach is that it will leave the external database running after completing the execution of the macro.  Whether this is desirable or not depends on each situation.  Below is a sample function to shell out a Macro:

'---------------------------------------------------------------------------------------
' Procedure : ShellExecExternalMacro
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a Macro in an external database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully quaified path and file name with extension of the external database
'             that you want to run a macro from
' sMacroName: Name of the Macro to run
'
' Usage:
' ~~~~~~
' Call ShellExecExternalMacro(Application.Currentproject.Path & "\Database2.accdb", "Macro1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Sep-16                 Initial Release
'---------------------------------------------------------------------------------------
Function ShellExecExternalMacro(sDb As String, sMacroName As String)
On Error GoTo Error_Handler
 
    Call Shell("msaccess.exe " & sDb & " /x " & sMacroName, 1)
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ShellExecExternalMacro" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Useful References:
How to use command-line switches in Microsoft Access

Use a Custom VBA Function

The best solution in my opinion is a simple custom function in which you can specify whether or not to close the external database instance.  Below is a sample function to do so:

'---------------------------------------------------------------------------------------
' Procedure : ExecExternalMacro
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a Macro in an external database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully quaified path and file name with extension of the external database
'             that you want to run a macro from
' sMacroName: Name of the Macro to run
' bCloseExtDb:Whether or not Access should close the instance of the external database
'
' Usage:
' ~~~~~~
' Call OpenExternalMacro(Application.Currentproject.Path & "\Database2.accdb", "Macro1", True)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Sep-16                 Initial Release
'---------------------------------------------------------------------------------------
Function ExecExternalMacro(sDb As String, sMacroName As String, bCloseExtDb As Boolean)
On Error GoTo Error_Handler
    Dim oAccess       As Access.Application
 
    Set oAccess = CreateObject("Access.Application")
 
    oAccess.OpenCurrentDatabase sDb
    oAccess.DoCmd.RunMacro sMacroName
 
Error_Handler_Exit:
    On Error Resume Next
    If bCloseExtDb = True Then oAccess.CloseCurrentDatabase
    Set appAccess = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ExecExternalMacro" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

So as you can see, there are a couple possible approaches to running a Macro in an external database, but VBA is involved (sorry Web Apps this is yet one more thing you can’t do!).

MS Access – Search with Spaces in Terms

MS Access Text Search with SpacesEvery developer at some point or another ends up needing to create a search functionality where their user(s) can enter what they know (terms, text, …) and pull up any matching records.  The problem being that you will quickly come to smack your head against your computer because once you setup a search textbox control and perform the most basic test, you will notice that Access truncates any spaces you enter making your search functionality completely useless.

Having faced this problem many years ago, and having recently been asked by fellow MVPs how to resolve this problem, I decided to create the following demo database.  In it, I demonstrate 2 similar approaches to work around Access’ native behavior and enable proper full text search.

Feel free to download your copy (Access 2007 – *.accdb) by using the link supplied below:

Text Search Demo

Windows 8 – Windows Defender Will not Turn On Error 577

I was recently trying to fix my father’s computer (Windows 8.1) by removing Avast antivirus and reactivating Windows Defender which is natively part of Windows 8.1.

So I thought this would be truly very easy; uninstall Avast and turn on Windows Defender, or so I thought.

After uninstalling Avast (which now has too many pop-ups) I went into the Action Center to reactivate Windows Defender.

Windows_Defender_Action_Center_Warning_MessagesBut no matter what I did (that is, no matter how many times I clicked on the “Turn on now” button, nothing happened- no error, no message, nothing)

So I then went to Avasts website and downloaded and ran their specialized uninstaller, but this didn’t help either.

Then I had the idea to check out the Windows Defender service and make sure it was running.  In fact, it wasn’t.  However, once I tried to start the service, I received an

Windows could not start the Windows Defender Service service on Local Computer.

Error 577 : Windows cannot verify the digital signature for this file.  A recent hardware or software change might have installed a file that is signed incorrectly or damaged, or that might be malicious software from an unknown source.

Windows_Defender_Error_577Then I did some online research and came across a post indicating that one of the possible issues was that Avast (or another program) had changed certain registry settings and set them to 0 (false):

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Defender\DisableAntiSpyware
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Defender\DisableAntiVirus

But even running regedit as the administrator, I could not change these settings and would receive a message:

Access is Denied

 

After taking a break, I finally decided to start uninstalling any other programs that could interfere with Windows Defender; other malware, spyware programs that I routinely install.  So I decided to start by uninstalling Spybot Search and Destroy.  BINGO!  After a reboot, I was able to edit the registry settings,  turn Windows Defender on through the Action center.

So nearly 3 hours after having started, what I thought was going to be a 5-10 minute process, I finally had things working properly.

So for anyone else smacking their heads against their computers trying to enable Windows Defender, take a look at any potential conflicting programs and if you have Spybot installed either disable it or uninstall it.  As an FYI – Spyware Blaster, Malwarebytes Anti-Malware, custom host file did not cause any problems and I left them installed (didn’t need to uninstall them to get Windows Defender up and running again).

Generate a Random String, Random Password in MS Access

Building on an old post MS Access VBA – Generate a Random String, I wanted to post a fully functional sample in which I added full support for multiple string generation.  I use this tool to generate a set of random passwords and then pick one to create a new account password…

The beauty of this tool over many others, is that you run it locally, not on someone else’s server (god knows if your actions are being logged with malicious intent) like all the online generators do (yes, call me paranoid – in today’s world one cannot be too safe).  This way, you remain in control.

You’ll also notice, that with this demo, you can control what type of string is generated:

  • Number of random strings to generate
  • String length
  • Apha
    • Lower case
    • Upper case
  • Numeric
  • Special Characters (that you can specify/edit)

Random String Generator

Feel free to download your copy (Access 2007 – *.accdb) by using the link supplied below:

Random String Generator – Demo Database (2007 accdb)

DoCmd.OutputTo in xlsx (Excel 2007+) Format

A common technique for exporting data to Excel is to utilize the  DoCmd.OutputTo Method.

Now, previously (pre Office 2007), the basic format to export a query to Excel format was simply:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLS, , True

BUT, once you port such a database to a post Office 2007 computer, you start to get it exported as a “Microsoft Excel 5.0/95 Workbook (*.xls)” format and such a file will thus open Excel in compatibility mode possibly given the user compatibility errors/messages when they go to save the file after working with it.

So unless you have to ensure legacy compatibility, it is strongly advisable to update your code to export the data in “Excel Workbook (*.xlsx)” format, the new Excel file format thus eliminating any compatibility issues. Hence, we simply need to change the OutputFormat format variable to a more appropriate format (acFormatXLS -> acFormatXLSX). So your code would thus become:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, , True

Alternately, you could also use:

DoCmd.OutputTo acOutputQuery, "YourQueryName", "Excel Workbook (*.xlsx)", , True

Looking to export to a different format?

Nothing could be easier! In the VBA Object browser, go to the Access Contants library and look over the various acFormat**** available to you. Such as:

  • acFormatHTML
  • acFormatPDF
  • acFormatRTF
  • acFormatSNP
  • acFormatTXT
  • acFormatXLS
  • acFormatXLSB
  • acFormatXLSX
  • acFormatPS

Taking Things a Little Further

It wouldn’t be very difficult to create a custom DoCmd.OutputTo function which could determine the version of MS Excel installed and the utilize the more appropriate OutputFormat variable. Then you code would be 100% compatible regardless of what your users have installed!

Are There Any Alternatives

Another approach available to us is simply automate Excel. This is the approach I have adopted. In my early development days, I used the built-in DoCmd.OutputTo command, but have long since abandoned it for my own custom export function: Export2XLS. Why? The custom function approach is fully compatible with whichever version of Excel a user has installed, and gives the programmer far more control over what the final export looks like. You can format Cells/Ranges, apply colors, fonts, …