Use system tables in access to save application variables

When you are developing an access application, you typically need to refer to many variables in forms and modules. To manage these variables, you can, of course, use Microsoft Visual Basic for Application (VBA) in the startup form to set their values, and then call a module that initializes the values.

However, after the application development is completed, the problem with using this method arises: Others may need to change those values at some point in the future without knowing where to start. I recommend creating a table named Systemtbl in the database, and then the user can access the table and modify the data when appropriate.

Creating Tables and Forms

After you create a table and add a variable to a new record in the table, you can create a form for the user that allows only modifications but cannot add or delete records, and the table should contain only one record that holds information about how to use it. Once you have saved all the variables, you can use VBA to retrieve the values of those variables when the application requires it.

For example, suppose an application needs to save the name and phone number of an application administrator, and as you've just learned, this information requires easy modification, and it doesn't make sense to write it into the code of a form or module.

The table has predefined one record and two fields (so far):

Table: Systemtbl

Field 1:adminname

Field 2:adminphone

When you need to display the appropriate names and phone numbers to the user on a form, you can use the OnOpen or onload events to populate the corresponding labels on the form, in this case, we name the labels "Phonelabel" and "Namelabel". Listing 1.10. A shows the code for this example function.

In the example function, we open the recordset in a snapshot-forwardonly way to reduce the resources used to access the recordset; then we apply the values of the fields in the table to the title of the name and phone labels.

Finally, close the recordset. Always remember to turn off the recordset, which accumulates as unwanted resources, resulting in reduced performance and even worse problems. The retrieval process is extremely fast because there is only one record in the system table. Now, you've added an extensible solution to a valid database application.