Microsoft Access Database
Architecture: Storing Temporary Data and User Settings
Provided by: Luke Chung, President of FMS, Inc.
There are many things a user does with an application that need to be
preserved either during processing, between screens, between sessions, or
between application updates/versions. When designing a system, it's
important to consider what needs to be kept and where/how to do this. If
designed properly, the data should also support multi-user environments.
Users are commonly annoyed to be forced to re-enter their last specifications when the
application should start with that as its default. After all, a computer is
supposed to be good at remembering things, right?
Keeping Selections in Memory for the Current Session
At the simplest level, the user's settings and can be stored in memory as
global variables in VBA. These
are temporary and will disappear when the application closes. However, while
it's open, the program can default to those values if
they should be used again.
Temporary variables, TempVars, introduced in Microsoft
Access 2007 can also be used and referenced via macros.
Using the Registry to Store User Information Between
Another way to save user preferences is to store it in
the user's Windows registry. This lets you store data on
a machine specific to the user for your application.
It's not appropriate for saving large amounts of data
that you would expect in a table but helpful for user
selections. VBA offers a few simple commands to manage
- SaveSetting appname,
section, key, setting
- DeleteSetting appname,
If you define your application name and where to store
the values, you can create, retrieve, and delete your
values there. Once you load them into your variables,
you can apply them as you would any global variable.
You'll need to make sure you define your application
name to not conflict with others.
Issues with Storing Data in the Registry
If you have a front-end/back-end split
database design, storing values in the registry lets you
update your front-end database without wiping out the
Of course, registry settings are only stored on that
machine, so if the user runs your application from another
machine, these settings will not exist for them. If this
is important, the settings should be saved in tables in
the back-end database for each user, then loaded when
Using Private Tables to Store Information Between
A nice thing about databases is that tables are available to store data and
lots of it.
Split Database Design with Temporary Tables in the Front
Tables can be used to save more data and may be preserved either locally
or centrally. In Microsoft Access, the common use of Jet databases with the
application database for each user linked to the central data database,
allows the front-end database to contain tables that are private to the user
and supports multi-user environments without collisions.
can also be located here if multiple steps are necessary to complete a
process, a complex report that requires multiple aggregations and selections
for example. For more information about split database architecture, visit
Microsoft Access Databases to Improve Performance and
For SQL Server applications, one can use tables that are
private to each user. These tables may be emptied each time the
process is finished or when the application closes, or remain untouched to
be available the next time the user is in the same section.
The nice thing about using tables to store user selections is that
it's automatically preserved the next time the program
is run. For instance, you may have a selection screen to
find some data. If that form is bound to a table that's
local to the user, the next time the form is opened, the
last selections are preserved. No programming is
However, these settings will disappear if you deploy a
new version of your application that replaces the user's
front-end copy of your database.
Previous Values Remain Valid
Anticipate that if your application is updated, you need
to make sure any previously saved settings are
appropriate. For instance, if your previous version
allowed saving a selection that is no longer valid,
you'll need to make sure that doesn't cause a problem in
your new version from a data validation or security
If that is a concern, storing the data in the front-end
database and deleting or resetting the values when a new
version is deployed will ensure previous values are not