Saving User Settings and Preferences in Microsoft Access Database

Microsoft Access provides several effective strategies for saving user settings and preferences, ensuring a personalized experience while maintaining data integrity across sessions and devices. In this article I will explore some of the possible methods we can use for storing user-specific and application-wide settings in Access databases.
 

Settings, What Settings?!

Here are but a few examples of the types of things one might store for an Access user:

  • Form positioning
  • Default filters
  • Datasheet column order, sizes and visibility
  • Default export format and output path
  • Colors, fonts,  …
  • Default printer
  • Language of preference
  • etc…

 

Using a Dedicated Table in the Database

A common approach is to create a dedicated table, such as ‘UserSettings’, within the Access database to store user-specific preferences. Each setting is then stored as a record within the table and can easily be access and CRUD operations performed.

In such a scenario, it make sense to create a couple UDF, such as: UserSetting_Get(), UserSetting_Set() & UserSetting_Delete() to ease working with the settings.

This approach ensures settings persist even after front-end updates or when switching devices, as long as the back-end database remains accessible.

You can even implement local synchronizing of the back-end data if you so choose, for performance reason, but normally, if you have best practices in place and a persistent connection, performance should not be an issue. What do I mean by this? Maintain a settings table in both the front-end and back-end databases. The front-end should primarily use its local copy of the settings table for faster access. If the front-end detects that its settings table is empty—such as after deploying a new version—it should automatically retrieve the settings from the back-end and store them locally for subsequent use. This approach combines the speed of local access with the reliability of centralized data, giving you the best of both worlds. Also don’t forget with such an approach when saving setting you need to update both tables.
 

Configuration Files

Settings can also be saved in configuration files (INI, .DAT, .JSON) stored locally or on a server. These are read by the front-end application during runtime.

For performance optimization, I setup my script to look locally for the configuration file, it is not found, then I go look on the server and copy it locally (for all future calls).  Also, when I do read the file, I read it to a VBA variable so it remain in memory for the duration of the session, so I only need to read the file once and everything is done thereafter in memory!

The drawback to this approach is that if you don’t take certain security steps, the files could be messed with by users.
 

Registry

Storing information in the Registry is another option for saving user preferences and Microsoft Access has built-in commands to do so: SaveSetting, GetSetting, GetAllSettings, DeleteSetting.

The main drawback here is that it is not portable across devices unless synchronized manually

You can learn more about working with the registry in my article:

VBA - Storing Information In The Registry
How can we store information: Settings Preferences Object States and positions … for a VBA solution, whether that be an Access database, Excel workbook, Word Document …? What about storing the information in the registry! That’s the subject I would like to broach in this article.
 

Final Thoughts

There is no one size fits all here, each approach has PROs and CONs and there usage depend on your setup and needs.

In all case, from a performance standpoint you are always best to read the values only once and then store the information in memory (VBA Variable, TempVar) instead of making repeated call to retrieve the information over and over.

One thing I would highlight for any of these, is for typical setting all is good, but if you plan on storing any potential hazardous or sensitive information be sure to first encrypt the data!