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.