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.

Other free downloads include our new Microsoft Access and SQL Azure and demonstration application which uses Microsoft Access and SQL Azure to leverage the power of cloud computing.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with 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

About these ads