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



