Mine the Registry’s Data Riches with Microsoft Access VBA and WMI

The Registry contains an enormous amount of data, some of which is very useful to the Microsoft Access developer. Microsoft Access VBA allows us to use WMI to access that data. We can get a list of all the System DSNs on a Computer, all of the ODBC drivers, all the Internet Cookies, list the paths of specific program files, get various security settings, list all startup commands and much more.

To begin gathering data, we add some Constants at the beginning of a Module.

Option Compare Database
Option Explicit

Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const HKEY_CURRENT_CONFIG = &H80000005

' Registry Value Type Constants:
Const REG_SZ = 1
Const REG_EXPAND_SZ = 2
Const REG_BINARY = 3
Const REG_DWORD = 4
Const REG_MULTI_SZ = 7

The following procedure reads a number of security settings stored in the Registry and can be used to read most HKEY_LOCAL_MACHINE Registry Keys. This procedure loops through an array of the values and uses the Select Case construct to read the differing Registry data types.

'---------------------------------------------------------------------------------------
' Read multiple type values from a Registry SubKey
'---------------------------------------------------------------------------------------
' Procedure : GetHKLMRegValues
' Purpose   : Use an Array to get security data from the Registry
' Example   : Call GetHKLMRegValues("SYSTEM\CurrentControlSet\Control\Lsa")
'---------------------------------------------------------------------------------------
'
Sub GetHKLMRegValues(strKeyPath As String)

    Dim objReg As Object
    Dim strComputer As String
    Dim estrValue As String
    Dim i As Long
    Dim arrEntryNames
    Dim arrValueTypes
    Dim arrValues
    Dim arrValue
    Dim byteValue
    Dim dwValue
    Dim strValue
    Dim lngCount As Long

    strComputer = "."

    Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    ' Use an array to access the data
    objReg.EnumValues HKEY_LOCAL_MACHINE, _
        strKeyPath, arrEntryNames, arrValueTypes

        Debug.Print "HKEY_LOCAL_MACHINE\" & strKeyPath
        Debug.Print "========================================"

    ' Loop through the array
    For i = 0 To UBound(arrEntryNames)
        lngCount = lngCount + 1
        Debug.Print " "
        Debug.Print "------------------" & lngCount & "-------------------"
        Debug.Print "Entry Name: " & arrEntryNames(i)
        ' Get the different types of array items values
        Select Case arrValueTypes(i)
            Case REG_SZ
                Debug.Print "Data Type: String"
                objReg.GetStringValue HKEY_LOCAL_MACHINE, _
                    strKeyPath, arrEntryNames(i), strValue
                Debug.Print "Value: " & strValue
            Case REG_EXPAND_SZ
                Debug.Print "Data Type: Expanded String"
                objReg.GetExpandedStringValue HKEY_LOCAL_MACHINE, _
                    strKeyPath, arrEntryNames(i), estrValue
                Debug.Print "Value: " & estrValue
            Case REG_BINARY
                Debug.Print "Data Type: Binary"
                objReg.GetBinaryValue HKEY_LOCAL_MACHINE, _
                    strKeyPath, arrEntryNames(i), arrValue
                For Each byteValue In arrValue
                    Debug.Print "Value: " & byteValue & " "
                Next
                Debug.Print vbCrLf
            Case REG_DWORD
                Debug.Print "Data Type: DWORD"
                objReg.GetDWORDValue HKEY_LOCAL_MACHINE, _
                    strKeyPath, arrEntryNames(i), dwValue
                Debug.Print "Value: " & dwValue
            Case REG_MULTI_SZ
                Debug.Print "Data Type: Multi String"
                objReg.GetMultiStringValue HKEY_LOCAL_MACHINE, _
                    strKeyPath, arrEntryNames(i), arrValues
                For Each strValue In arrValues
                    Debug.Print strValue
                Next
        End Select
    Next i

    Set objReg = Nothing

End Sub

The code can be copied and modified to create another procedure that will read data from other Root Keys. This can be done by changing HKEY_LOCAL_MACHINE, for example, to HKEY_CURRENT_USER wherever it is found in the procedure. An example can be seen in the WMISample database that you can download.

Another example of useful data stored in the Registry is the Country Codes. The Country Codes specify the country/region code for the user’s language and are based on international phone dialing prefixes. They are used to specify a user’s time and date formats. This is useful when using Office Automation to work with phone numbers in Outlook.

Country Codes are used, along with other data, to establish settings for international time, dates, currency, case conversions, decimal separators, and language conversion. This enables Access developers to produce Globalized applications that can be used around the world.

It can also be very helpful to have a list of Countries in a combo box for a user to select. The following code demonstrates how to easily fill a table with a list of Country Codes and Country names from the Registry. The table only needs two fields:

Field Name: CountryID; DataType: Text; Primary Key
Field Name: CountryName; DataType: Text

'---------------------------------------------------------------------------------------
' Procedure : GetRegCountryCodes
' Purpose   : Get Country Names matched to Numbers from the Registry
' Example   : Call GetRegCountryCodes("tblCountries")
'---------------------------------------------------------------------------------------
'
Sub GetRegCountryCodes(strTable As String)
On Error Resume Next

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objReg As Object
    Dim strComputer As String
    Dim strKeyPath As String
    Dim strValue As String ' The Entry Value
    Dim strValueName As String
    Dim arrSubKeys
    Dim SubKey

    strComputer = "."

    Set db = CurrentDb

    'Set up the file.
    Set rst = db.OpenRecordset(strTable, dbOpenDynaset)

    ' The NamesSpace is not "\root\cimv2" but "\root\default:StdRegProv"
    Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    ' The Registry Path we need to access
    strKeyPath = _
        "SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List"

    strValueName = "Name" ' The name of the Entry from which to get the value

    ' Create an Array of the Subkeys
    objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

    ' Loop through the Array Add the records to the Table
    For Each SubKey In arrSubKeys
        ' Get the Country name from the Subkey Entry "Name" using the strValue variable
        objReg.GetStringValue HKLM, strKeyPath & "\" & SubKey, strValueName, strValue
        ' Append the information to the Table
        rst.AddNew
        rst!CountryID = SubKey
        rst!CountryName = strValue
        rst.Update
    Next

    rst.Close

    Set objReg = Nothing
    Set rst = Nothing
    Set db = Nothing

End Sub

We saw in an earlier article about the WMI Win_32OperatingSystem class that our Current User’s Country Code was one of the values that was returned. Instead of displaying the Country Code number we called a procedure that used the Select Case construct to get the name of the Country. My Registry lists 236 Countries. Typing out all 236 “Cases” can be tedious. But we can use the following code to quickly and easily build the entire procedure and save it to a text file or a bas file. Then all we have to do is import it as a module or copy the code and paste it in the Visual Basic Editor.

'---------------------------------------------------------------------------------------
' Procedure : SaveSelectCaseCountries
' Author    : Patrick Wood
' Purpose   : Save Procedure Select Case Country Names from the Registry as a text file
' Example   : Call SaveSelectCaseCountries("C:\CountriesSelectCase.txt") or
'           : Call SaveSelectCaseCountries("C:\CountriesSelectCase.bas")
'---------------------------------------------------------------------------------------
'
Sub SaveSelectCaseCountries(strTxtFilePath As String)
    Dim objReg As Object
    Dim strComputer As String
    Dim strKeyPath As String
    Dim strValue As String
    Dim strValueName As String
    Dim fso As Object
    Dim fsoFile As Object
    Dim arrSubKeys
    Dim SubKey

    strComputer = "."

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.CreateTextFile(strTxtFilePath)

    Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = _
        "SOFTWARE\Microsoft\Windows\CurrentVersion\Telephony\Country List"

    strValueName = "Name"

    objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

    fsoFile.WriteLine "Option Compare Database"
    fsoFile.WriteLine "Option Explicit"
    fsoFile.WriteBlankLines (1)
    fsoFile.WriteLine "Function GetCountryName(strNumber As String) As String"
    fsoFile.WriteBlankLines (1)
    fsoFile.WriteLine "    Select Case strNumber"

    For Each SubKey In arrSubKeys
        objReg.GetStringValue HKLM, strKeyPath & "\" & SubKey, strValueName, strValue
        ' Write the value as a String
        fsoFile.WriteLine "        Case """ & SubKey & """"
        fsoFile.WriteLine "            GetCountryName = """ & strValue & """"
    Next

    fsoFile.WriteLine "        Case Else"
    fsoFile.WriteLine "            GetCountryName = ""Unknown"""
    fsoFile.WriteLine "    End Select"
    fsoFile.WriteBlankLines (1)
    fsoFile.WriteLine "End Function"

    fsoFile.Close

    Set fsoFile = Nothing
    Set fso = Nothing
    Set objReg = Nothing

End Sub

Below is a sample of the code produced.

Option Compare Database
Option Explicit

Function GetCountryName(strNumber As String) As String

    Select Case strNumber
        Case "1"
            GetCountryName = "United States"
        Case "101"
            GetCountryName = "Anguilla"
        Case "102"
            GetCountryName = "Antigua and Barbuda"
        Case "103"
            GetCountryName = "Bahamas, The"
        Case "104"
            GetCountryName = "Barbados"
        Case "105"
            GetCountryName = "Bermuda"

The previous procedure received the Country Code Arguments as String but they can also be used as Integers as shown in the SaveSelectCaseCountryNo Function in the sample database.

This is just as small sample of the data contained in the Registry that is useful for application development. We will learn more about how VBA can manage the Registry using WMI in the articles to come.

You can download the free sample database, WMISample.zip which contains the code used in this article plus other code samples. The code used in this article is located in the PAWWMIRegistry Module.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software now with new Contributions management.
Code Samples

Are your emails stored in compliance with new government regulations and electronic document discovery legal requirements? Get the New Email Archiver

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Read or Write to the Registry with One Line of Microsoft Access Code

Microsoft Access VBA enables you to store a value in the Registry with just one line of code. You can also read the stored value with one line of code. This is possible using Access’ built-in code which has its own section of the Registry. Jimmy Peña, author of the excellent Code For Excel And Outlook Blog, reminded me of this in a comment about my last Article. Because this subject deserves more than a passing reference, I did not mention it then so I could give it due justice in another article. Even though this article does not use WMI, I want to share this information while we are on the subject of the Registry because it is a very handy tool to add to your VBA toolbox.

Using the Registry is a useful alternative to Global Variables, which are prone to loose their value when an untrapped error occurs. You can save connection strings, Form colors, Language settings, a most recently used list, user preferences, or custom user permissions which you can apply using code.


The SaveSetting Statement

The the SaveSetting Statement saves data to the “HKEY_CURRENT_USER\Software\VB and VBA Program Settings” hive.

SaveSetting CurrentDb.Properties(“AppTitle”), “StoredData”, “MyKey”, “My data.”

This will save “My Data” as the value of the “MyKey” key located at “HKEY_CURRENT_USER\Software\VB and VBA Program Settings\WMISample\StoredData” in the Registry if you are using the latest WMISample Database. If you are using a different database your Application Title will be in the place of “WMISample”.

The SaveSetting Statement has four arguments: appname, section, key, and setting.

• appname – A required string expression containing the database Application Title.
• section – A required string expression containing the name of the section under which the key is to be set.
• key – The name of the key you are setting.
• setting – A required expression of any data type that defines the value.

appname
We are using CurrentDb.Properties(“AppTitle”) to get the Application Title under which we will save all our data. You can actually use any string value for this argument but it is most helpful to store all settings and data under the Application or Project Name since there are often a number of databases on a computer.

The Application Title appears in the Application Window Title Bar and it is the Database Property named “AppTitle”. You must set the Application Title property prior to using the SaveSetting Statement or the code will fail to work. It is easily set using the Menu Bar by clicking Tools > Startup and entering the title of your choice in the Application Title textbox.

You can also set the Application Title using code. However this requires first creating the custom database property “AppTitle”. Why do you have to create the property when you can already use it in code? Who knows? It is strange, but true (at least on my computer). Creating custom database properties is a subject we will leave for another article.

Using the CurrentProject.Name for the appname is easy, but be aware that your code will be broken if the name of the database file is changed. This is how the CurrentProject.Name is used as the appname:

SaveSetting CurrentProject.Name, “StoredData”, “MyKey”, “My data.”

section
There can be several different sections under each appname. Sections can be named according to your choice.

key
You can also choose any name for the key which is a String REG_SZ key.

setting
This is the value of the key. It can be the name of the last used Form, a connection string, a number, or any value you want to store.


The GetSetting Function

It also takes only one line of code to read the data you have stored in the Registry using the GetSetting Function.

Debug.Print GetSetting(CurrentDb.Properties(“AppTitle”), “StoredData”, “MyKey”)

You can also use the following examples to get the correct value from the key.

Sub ReadRegKeyValue()

    Dim strValue As String

    Debug.Print GetSetting(CurrentDb.Properties("AppTitle"), _
        "StoredData", "MyKey", "myDefault")

    strValue = GetSetting(CurrentDb.Properties("AppTitle"), _
        "StoredData", "MyKey", "Default")

    Debug.Print strValue

End Sub

The GetAllSettings Function

All of the keys and their values in a section can be retrieved using the GetAllSettings Function as demonstrated in the following procedure:

 Sub GetAllKeyValues()

    Dim varData As Variant
    Dim i As Integer

    varData = GetAllSettings(CurrentDb.Properties("AppTitle"), "StoredData")

    For i = LBound(varData, 1) To UBound(varData, 1)
        Debug.Print varData(i, 0) & " - " & varData(i, 1)
    Next i

End Sub

The DeleteSetting Function

The DeleteSetting Function deletes a section or just a key if the key name is used.

DeleteSetting CurrentDb.Properties(“AppTitle”), “StoredData”, “MyKey”

The next example demonstrates deleting a section and all of its keys and values.

DeleteSetting CurrentDb.Properties(“AppTitle”), “StoredData”


Conclusion

Even though Access’s built-in Registry capabilities are limited, they are very easy to use and are useful for storing custom data and settings that persist, remaining available whenever the database is opened.

We will learn more about how VBA can manage the Registry using WMI in the articles to come.

You can download the free sample database, WMISample.zip which contains the code used here at our
Gaining Access website. The code is located in the PAWWMIRegistry Module.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Are your emails stored in compliance with new government regulations and electronic document discovery legal requirements? Get the New Email Archiver

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access VBA Uses WMI to Read the Registry

Microsoft Access VBA can read and write to the Windows Registry by using WMI. In this article we are going to show you how easy it is to read 5 different types of Registry Values with VBA and WMI.

The Registry provided a solution for the problem caused by the proliferation of separate ini settings files which often contained conflicting settings. The Registry became the central repository for data and settings reducing conflicts and providing better organization for the operating system, hardware, services, and applications settings. Most changes made concerning a computer and software are stored in the Registry.

The Registry is useful for the Access developer for storing connection strings, user preferences, a last used list, default startup settings and more. Since the Registry is vital to the operating system, hardware, services, and software it contains a very large and varied amount of data. Within this vast amount of data are treasures of information to be found. So let us begin by examining how we can read data from the Registry.

At first glance the Registry may seem very complicated, but actually the registry contains just two main types of elements: Keys and Values. The Keys are very similar to folders and subfolders having a similar hierarchy of Keys and SubKeys. The Registry Values are Name and Data pairs which are very much like the Properties and their Values we use in VBA code.

The top level Registry Keys are organized into Root Keys also known as Hives. The Root Keys on my XP are:

• HKEY_CLASSES_ROOT
• HKEY_CURRENT_USER
• HKEY_LOCAL_MACHINE
• HKEY_USERS
• HKEY_CURRENT_CONFIG

The most common Registry Value Names and their Constants are:

• String: REG_SZ = 1
• Expanded String: REG_EXPAND_SZ = 2
• Binary: REG_BINARY = 3
• DWord: REG_DWORD = 4
• Multi-String: REG_MULTI_SZ = 7

I will not take the time to explain them all here since ample information about Registry Value Types can easily be found on the internet.

Before I forget I need to give the usual, but important, warning that making changes to the Registry can cause damage to your software and operating system so if you are smart you will back up the Registry before making changes. To do that click on Start, then select Run, enter “Regedit” without the quotes and then click “OK”. After Regedit opens click on File and then Export to export your Registry to a backup file. This will probably take a few minutes, but it is better than hours of tears because you lost your loved ones photos!

The Registry stores information in several different types and using WMI we can read all the types listed here. We will start by declaring the Root Key Constants at the beginning of the Module.

Option Compare Database
Option Explicit

Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const HKEY_CURRENT_CONFIG = &H80000005

Most of this first part of the procedures is familiar to you if you have been following our articles. However in these procedures we access a different WMI root. When we Set the objReg we use “\root\default:StdRegProv” instead of “\root\cimv2″.

The first example demonstrates how to read is the simple String Value.

Sub GetRegString()
    Dim objReg As Object
    Dim strComputer As String
    Dim strKeyPath As String
    Dim strValueName As String
    Dim strValue

    strComputer = "."

     Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE"
    strValueName = "Path"

    objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
    Debug.Print "Microsoft Access Location: " & strValue

    Set objReg = Nothing

End Sub

The next value to read is the often used DWord Value. In this procedure we use a Select Case construct to give meaning to the DWord value. In this case it is the Windows Automatic Updates setting.

Sub GetRegDWord()
    Dim objReg As Object
    Dim strComputer As String
    Dim strKeyPath As String
    Dim strValueName As String
    Dim strDWordValue As String
    Dim dwValue

    strComputer = "."

     Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update"
    strValueName = "AUOptions"

    objReg.GetDWORDValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, dwValue

    ' If there is no DWord Value we want "Null" to be returned
    strDWordValue = Nz(dwValue, "Null")

    Select Case strDWordValue
        Case "2"
            strDWordValue = strDWordValue & " - Notify for download and notify for install."
        Case "3"
            strDWordValue = strDWordValue & " - Auto download and notify for install."
        Case "4"
            strDWordValue = strDWordValue & " - Auto download and schedule the install."
        Case "Null"
            strDWordValue = "Automatic Updates status is Unknown - No value was returned."
        Case Else
            strDWordValue = strDWordValue & " - Automatic Updates are not enabled."
    End Select

    Debug.Print strDWordValue

    Set objReg = Nothing

End Sub

The next value to read is the Multi-String Value. In this procedure we will get a list of Event Log Sources. In a later article we will show how to filter and save specific Event Logs Text which contain important data.

' Example Call SaveRegSystemEventLog("C:\SystemEventLogSources.txt")
Sub SaveRegSystemEventLog(strTextFilePath As String)
    Dim objReg As Object
    Dim fso As Object
    Dim fsoFile As Object
    Dim strComputer As String
    Dim strValueName As String
    Dim strKeyPath As String
    Dim arrValues
    Dim arrValue

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.CreateTextFile(strTextFilePath)

    strComputer = "."

     Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = "SYSTEM\CurrentControlSet\Services\Eventlog\System"
    strValueName = "Sources"

    objReg.GetMultiStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
        strValueName, arrValues

    For Each arrValue In arrValues
        fsoFile.WriteLine arrValue
'        Debug.Print arrValue
    Next

    fsoFile.Close

    Set fsoFile = Nothing
    Set fso = Nothing
    Set objReg = Nothing

End Sub

The next value to read is the Expanded String Value.

Sub ReadExpandedRegString()

    Dim objReg As Object
    Dim strComputer As String
    Dim strValueName As String
    Dim strKeyPath As String
    Dim strValue

    strComputer = "."

    Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\WinLogon"
    strValueName = "UIHost"

    objReg.GetExpandedStringValue HKEY_LOCAL_MACHINE, strKeyPath, _
        strValueName, strValue

    Debug.Print "The Windows logon UI host is: " & strValue

    Set objReg = Nothing

End Sub

Lastly, we see the code to read a binary registry value. In this case the code reads the binary AccessName Value.

Sub ReadBinaryRegValue(strTextFilePath As String)

    Dim objReg As Object
    Dim fso As Object
    Dim fsoFile As Object
    Dim strComputer As String
    Dim strKeyPath As String
    Dim strValueName As String
    Dim i As Long
    Dim varValue

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFile = fso.CreateTextFile(strTextFilePath)

    strComputer = "."

    Set objReg = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\default:StdRegProv")

    strKeyPath = "Software\Microsoft\Office\11.0\Access"
    strValueName = "AccessName"

    objReg.GetBinaryValue HKEY_CURRENT_USER, strKeyPath, _
        strValueName, varValue

    For i = LBound(varValue) To UBound(varValue)
        fsoFile.WriteLine varValue(i)
    Next

    fsoFile.Close

    Set fsoFile = Nothing
    Set fso = Nothing
    Set objReg = Nothing

End Sub

We will learn more about how VBA can manage the Registry using WMI in the articles to come.

You can download the free sample database, WMISample.zip which contains the code used here at our Gaining Access website. The code is located in the PAWWMIRegistry Module.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Are your emails stored in compliance with new government regulations and electronic document discovery legal requirements? Get the New Email Archiver

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access VBA Uses WMI to Control Windows Services

You can Stop, Start, Pause, and Resume Windows Services with Microsoft Access VBA by using the power of WMI (Windows Management Instrumentation). This is called “setting the State” of the Service. This can be useful, for example, if we want to make sure the SQL Server Reporting Services is running. We may also want to stop a service we do not need that is using up computer resources.

We can use the “Update Services Data” button on our Form to get the current information about all Windows Services on our computer. The current State of the Service is displayed in the “State” text box, which is encircled in orange.

Our Form provides essential information we need before we attempt to set the state of the Service. The “AcceptPause” value informs us if we can Pause the Service and the “AcceptStop” value indicates if the Service can be Stopped.

If we want to change the State of a Service we use the Combo box encircled in Red. The label displays the different States of a Service which are used as a Value List of the Combo box. They are Start, Stop, Pause, and Resume. When we select the desired State the Combo box AfterUpdate Event is used to set the State of the Service.

Private Sub cmdStopService_Click()

    Dim strServiceState As String
    Dim strServiceName As String

    ' Get the name of the current record's Service
    strServiceName = Me.txtName.Value

    ' Get the Selected State
    strServiceState = Me.cboSetServiceState.Value

    ' Call a Function to set the Service State
    ' passing the Service Name and the selected State
    Call SetServiceState(strServiceName, strServiceState)

End Sub

We could have used a button and a Function to set each State, but we decided to use just one procedure to set any of the four possible States. Much of the code in the next procedure has been discussed in previous articles.

Function SetServiceState(strServiceName As String, strServiceState As String) As Long

    Dim objWMIService As Object
    Dim colServices As Object
    Dim objService As Object
    Dim strComputer As String
    Dim strErrorMessage As String
    Dim errReturnCode As Long

    DoCmd.Hourglass True

    ' Set an error code not used by Microsoft
    errReturnCode = 9999

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\cimv2")

    Set colServices = objWMIService.ExecQuery _
        ("SELECT * FROM win32_Service" & _
        " WHERE Name = '" & strServiceName & "'")

    For Each objService In colServices
         ' Use Select Case to set the chosen Service State
        Select Case strServiceState
            Case "Start"
                errReturnCode = objService.StartService()

            Case "Stop"
                errReturnCode = objService.StopService()

            Case "Pause"
                errReturnCode = objService.PauseService()

            Case "Resume"
                errReturnCode = objService.ResumeService()
        End Select
    Next

    DoCmd.Hourglass False

    ' Get and display the results of the code
    Select Case errReturnCode
        Case Is = 0
            MsgBox "Service " & strServiceName & " has been changed to " _
                & strServiceState & " successfully.     ", vbInformation
        Case Is = 9999
            ' If errReturnCode has not changed the Service was not found
            MsgBox "Service """ & strServiceName & """ was not found" & _
                " and may not exist.       ", vbCritical
        Case Else
            ' Get the error messages supplied by Microsoft
            strErrorMessage = ErrReturnStateChange(errReturnCode)
            MsgBox "Service " & strServiceName _
                & " State change failed.      " & vbCrLf & "Reason: " _
                & strErrorMessage & "       ", vbExclamation _
                , "Service State Change Failed"
    End Select

    SetServiceState = errReturnCode

    Set objService = Nothing
    Set colServices = Nothing
    Set objWMIService = Nothing

End Function

Again we have used the error documentation provided by Microsoft to write the error code return procedure. Fortunately, the error codes for all four of the State Change Methods are the same, which enables us to use just one procedure.

Function ErrReturnStateChange(lngerrReturn As Long) As String

    Select Case lngerrReturn
        Case Is = 0
            ErrReturnStateChange = "Success"
        Case Is = 1
            ErrReturnStateChange = "Not Supported"
        Case Is = 2
            ErrReturnStateChange = "Access Denied"
        Case Is = 3
            ErrReturnStateChange = "Dependent Services Running"
        Case Is = 4
            ErrReturnStateChange = "Invalid Service Control"
        Case Is = 5
            ErrReturnStateChange = "Service Cannot Accept Control"
        Case Is = 6
            ErrReturnStateChange = "Service Not Active"
        Case Is = 7
            ErrReturnStateChange = "Service Request timeout"
        Case Is = 8
            ErrReturnStateChange = "Unknown Failure"
        Case Is = 9
            ErrReturnStateChange = "Path Not Found"
        Case Is = 10
            ErrReturnStateChange = "Service Already Stopped"
        Case Is = 11
            ErrReturnStateChange = "Service Database Locked"
        Case Is = 12
            ErrReturnStateChange = "Service Dependency Deleted"
        Case Is = 13
            ErrReturnStateChange = "Service Dependency Failure"
        Case Is = 14
            ErrReturnStateChange = "Service Disabled"
        Case Is = 15
            ErrReturnStateChange = "Service Logon Failed"
        Case Is = 16
            ErrReturnStateChange = "Service Marked For Deletion"
        Case Is = 17
            ErrReturnStateChange = "Service No Thread"
        Case Is = 18
            ErrReturnStateChange = "Status Circular Dependency"
        Case Is = 19
            ErrReturnStateChange = "Status Duplicate Name"
        Case Is = 20
            ErrReturnStateChange = "Status - Invalid Name"
        Case Is = 21
            ErrReturnStateChange = "Status - Invalid Parameter"
        Case Is = 22
            ErrReturnStateChange = "Status - Invalid Service Account"
        Case Is = 23
            ErrReturnStateChange = "Status - Service Exists"
        Case Is = 24
            ErrReturnStateChange = "Service Already Paused"
    End Select

End Function

So to change the State of a Windows Service with our Form, we first update the Services data with the “Update Services Data” button. Then we use the “Find Service” Combo box to go to the record of the selected Service. We can then read the “State” text box to see if the Service’s State is already set to what we want. We also need to check the “Start Mode” text box to see if the Service is Disabled. If so, we need to change the Start Mode to “Manual” or “Automatic” before we can set the State of the Service. We should also look at the “AcceptPause” and the “AcceptStop” text boxes to determine if we can set the State we want. We can then set the State of the Process using the Start, Stop, Pause, Resume Service combo box.

We look forward to discovering more of the power of using WMI with VBA in our next article.

You can download the free sample database, WMISample.zip which contains the Form and the code used in this article at our Gaining Access website.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Are your emails stored in compliance with new government regulations and electronic document discovery legal requirements? Get the New Email Archiver

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,

Patrick (Pat) Wood
Gaining Access

Microsoft Access Controls Windows Services

You can Stop, Start, Pause, Resume, Disable, and Enable Windows Services with Microsoft Access VBA. Access VBA can do this by using the power of WMI (Windows Management Instrumentation). We are able to do this by using late binding to access the WbemScripting library. With late binding you do not have to worry about setting References or having broken references. But if you want to use early binding or use the Object Browser to learn more about using WMI you can set a reference to the Microsoft WMI Scripting V1.2 Library. The full name and path is C:\Windows\System32\wbem\wbemdisp.tlb on my computer.

In our last article we described Windows Services as the means by which Programs or Windows Processes to get things done as well as carry out vital functions for computer operations.

We recommend again that caution is used because changing Services settings can ruin a computer’s ability to function. It is vital to gain the knowledge necessary to safely manage Windows Services. We recommend a close reading of the Win32_Service Class webpage and related pages that document the Win32_Service Class Methods.

We want to show how you can use Access to stop and start Services which requires changing the “State” of the Service. But first we have know whether or not a Service is already running and if it can be started or stopped because you can’t start a Service if it is Disabled. You have to change the Start Mode first.

There are five Start Modes: Boot, System, Automatic, Manual, and Disabled. Using VBA and WMI provides even greater control than is provided by the Microsoft Management Console (MMC) Services Snap-in where your choices are usually just Automatic, Manual, or Disabled.

Microsoft provides the following information about the Start Mode of Services:

• Boot: Device driver started by the operating system loader. This value is valid only for driver services.
• System: Device driver started by the operating system initialization process. This value is valid only for driver services.
• Automatic: Service to be started automatically by the Service Control Manager during system startup.
• Manual: Service to be started by the Service Control Manager when a process calls the StartService method.
• Disabled: Service that can no longer be started.

As you can see from the list, it would be best if we do not change the Start Mode of most Services since changing from System to Boot, for example, may cause the Service to no longer work properly. No doubt that is why the MMC limits our Start Mode options. But there are some unwanted services we may want to to change to Disabled so they will not run at all. And there may be some services that we want to change from Disabled to Manual or Automatic so the Service will start as needed.

One of the benefits of using the Win32_Services Form is that we can gain information about all the services on our computer. It is very important to click the “Update Services Data” button to get the current information about a Service because old data may be incorrect. The Description is very helpful. We can see if the Service is Started, and the State informs us whether the service is running, stopped, or paused. We especially need to see the Start Mode, which we are preparing to set.

To enable changing the Start Mode of the Service in the Current Record we added a Combo box to the form. We have named our Combo box “cboSetStartMode”, set the Row Source Type to “Value List”, and added the following to the Row Source Property: Boot;System;Automatic;Manual;Disabled.

In the Combo box AfterUpdate Event we have this code:

Private Sub cboSetStartMode_AfterUpdate()

    Dim strServiceName As String
    Dim strSetStartMode As String

    ' Get the Name of the Service listed in this Record
    strServiceName = Me.txtName.Value
    ' Get the Start Mode selected in the Combo box
    strSetStartMode = Me.cboSetStartMode.Value
    ' Call the procedure to change the Start Mode
    Call SetStartMode(strServiceName, strSetStartMode)

End Sub

This procedure calls the SetStartMode Sub. The critical code of the SetStartMode procedure is errReturnCode = objService.ChangeStartMode(strStartMode). This code attempts to change the Start Mode and returns an error code integer signifying Success with 0 or an Error with any other value.

Since the errReturnCode’s default value is 0, we set it to a value not used by Windows near the beginning of the procedure, using the number 9999. We do this to avoid a silent, but significant error. If we did not change this value from 0, we could easily get a false return that the Start Mode had been successfully changed, when in fact it had not been changed at all! This would happen if there was no instance of the Service found, which would cause the line of code errReturnCode = objService.ChangeStartMode(strStartMode), that changes the Start Mode and gets the error return value, to be skipped.

If the errReturnCode remains at 9999 a Message box appears to report the error. Other error codes are changed to the corresponding error text by calling the errReturnStartMode Function.

Sub SetStartMode(strServiceName As String, strStartMode As String)

    Dim objWMIService As Object
    Dim colServices As Object
    Dim objService As Object
    Dim strComputer As String
    Dim strErrorMessage As String
    Dim errReturnCode As Long

    DoCmd.Hourglass True

    ' Set an error code not used by Microsoft
    errReturnCode = 9999

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\" _
        & strComputer & "\root\cimv2")

    Set colServices = objWMIService.ExecQuery _
        ("SELECT * FROM win32_Service" & _
        " WHERE Name = '" & strServiceName & "'")

    For Each objService In colServices
        ' Use the ChangeStartMode Method to change the Start Mode
        ' errReturnCode informs us of success or errors
        errReturnCode = objService.ChangeStartMode(strStartMode)
    Next

    DoCmd.Hourglass False

    ' Get the Success or Error message
    Select Case errReturnCode
        Case Is = 0
            MsgBox "Service " & strServiceName & " has been set to " _
                & strStartMode & " Start Mode successfully.     " _
                , vbInformation
        Case Is = 9999
            ' If errReturnCode has not changed the Service was not found
            MsgBox "Service """ & strServiceName & """ was not found" & _
                " and may not exist.       ", vbCritical
        Case Else
            ' Get the error messages supplied by Microsoft
            strErrorMessage = errReturnStartMode(errReturnCode)
            MsgBox "Service " & strServiceName _
                & " Start Mode change failed.     " & vbCrLf _
                & "Reason: " & strErrorMessage & "        ", _
                vbExclamation, "Service Start Mode Change Failed"
    End Select

    Set objService = Nothing
    Set colServices = Nothing
    Set objWMIService = Nothing

End Sub

When the errReturnCode is passed to this Function the error message provided by Microsoft is returned. We used data from Microsoft’s ChangeStartMode Method of the Win32_Service Class web page to build the Function.

Function errReturnStartMode(lngerrReturn As Long) As String

    Select Case lngerrReturn
        Case Is = 0
            errReturnStartMode = "Success"
        Case Is = 1
            errReturnStartMode = "Not Supported"
        Case Is = 2
            errReturnStartMode = "Access Denied"
        Case Is = 3
            errReturnStartMode = "Dependent Services Running"
        Case Is = 4
            errReturnStartMode = "Invalid Service Control"
        Case Is = 5
            errReturnStartMode = "Service Cannot Accept Control"
        Case Is = 6
            errReturnStartMode = "Service Not Active"
        Case Is = 7
            errReturnStartMode = "Service Request Timeout"
        Case Is = 8
            errReturnStartMode = "Unknown Failure"
        Case Is = 9
            errReturnStartMode = "Path Not Found"
        Case Is = 10
            errReturnStartMode = "Service Already Running"
        Case Is = 11
            errReturnStartMode = "Service Database Locked"
        Case Is = 12
            errReturnStartMode = "Service Dependency Deleted"
        Case Is = 13
            errReturnStartMode = "Service Dependency Failure"
        Case Is = 14
            errReturnStartMode = "Service Disabled"
        Case Is = 15
            errReturnStartMode = "Service Logon Failure"
        Case Is = 16
            errReturnStartMode = "Service Marked For Deletion"
        Case Is = 17
            errReturnStartMode = "Service No Thread"
        Case Is = 18
            errReturnStartMode = "Status Circular Dependency"
        Case Is = 19
            errReturnStartMode = "Status Duplicate Name"
        Case Is = 20
            errReturnStartMode = "Status Invalid Name"
        Case Is = 21
            errReturnStartMode = "Status Invalid Parameter - Selected Start Mode is Invalid"
        Case Is = 22
            errReturnStartMode = "Status Invalid Service Account"
        Case Is = 23
            errReturnStartMode = "Status Service Exists"
        Case Is = 24
    errReturnStartMode = "Service Already Paused"
End Select

End Function

So to change the Start Mode of a Windows Service with our Form, we use the “Find Service” Combo box to move to the Service’s Record. Next we carefully note the information on the Form about the Service so we make a safe decision about what we plan to do. Then we use the “Set Start Mode” Combo box to change the Service Start Mode. The message box will then let us know whether or not the Start Mode was changed, and if not, the reason for the failure.

In our next article we plan to demonstrate starting, stopping, pausing, and resuming services.

You can download the free sample database, WMISample.zip which contains the Form and the code used in this article at our Gaining Access website.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,

Patrick (Pat) Wood
Gaining Access