Storing Settings in MS Access – Directly in Tables, with IntelliSense

Store user-defined settings in Access – without INI files and without the registry. This solution keeps settings directly in tables and manages them with one-line VBA commands that even support IntelliSense.

MS Access databases are known for their flexibility. However, the question often arises as to the best way to store user-defined settings. Common methods such as external INI files or the Windows registry each have their advantages and disadvantages. Here I present an elegant solution to store, retrieve, change and delete settings directly in Access tables.

Forget complicated recordset queries: with simple VBA functions that even support IntelliSense, you manage settings with just one line of code. The example stores settings in the frontend and backend – but it can easily be extended to other tables, such as a user-settings table saved in the user profile.

Why this approach?

• Simplicity: short, intuitive commands – no complex recordset operations.
• IntelliSense support: faster and error-free code entry.
• Efficiency: fast reading and writing of settings.

What is required?

• tblSettingsFE, tblSettingsBE
• modSettings
• clsSettings

The tables

The two tables are built as follows:

The modSettings module

' DECLARATIONS ---------------------------------------------------------------
Private Const settingsBE As String = "tblSettingsBE"   ' table for the backend settings
Private Const settingsFE As String = "tblSettingsFE"   ' table for the frontend settings
'-----------------------------------------------------------------------------

' ENUMERATIONS ---------------------------------------------------------------
' Put all setting names in here (IntelliSense)
Public Enum eSettings
    ' Frontend Settings (1 - 99)
    f_BackendPath = 1
    f_Version     = 2
    f_Programmer  = 3
    ' ...
    ' Backend Settings (100 - XXX)
    b_BackupPath  = 100
    b_BackupDays  = 101
    b_ProjectName = 102
    ' ...
End Enum

The setting names are entered in the eSettings enumeration – necessary for IntelliSense to work. You do not need to enter them in the tables; that is handled by the newSetting() function. This lets you create new settings straight from the VBE without adding them manually in the backend (handy when you don’t have access to a customer’s backend).

The values are stored as a string; the field data type is specified when creating with newSetting(). When retrieved, the string is converted back to the correct type based on that data type.

The commands

' Create a new setting in the frontend
newSetting f_BackendPath, "D:\backend", eString

' Change a setting in the frontend
setSetting f_BackendPath, "E:\backend"

' Read a setting from the backend
Debug.Print getSetting(b_BackupPath)

' Delete a setting from the backend
delSetting b_ProjectName

You’ll find the detailed description and all notes directly in the code, well documented as usual.

Download