Microsoft Access Database Architecture: 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.

At the simplest level, the user's settings can be stored in memory. These are temporary and will disappear when the form or application closes. If there are more settings and a desire to preserve it across sessions (on that machine), registry settings can be used.

Tables should be used to save more data and may be preserved either locally or centrally. In 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. Temporary tables 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 SQL Server applications, one should 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. Nothing is more annoying to users than having to re-enter their last specifications when the application should start with that as its default.