Data Management

Using a system table to store application variables in Access

In most Access applications, you maintain a set of system-wide variables in all of your forms and modules. Here's an easy way to make those variables available for editing by the end user.

By Todd E. Parker

When you develop an Access application, you typically need to refer to a number of variables throughout various forms or modules in your application. To manage those variables, you can, of course, use Microsoft Visual Basic for Applications (VBA) to set their values with a startup form that then calls a module, which then initializes these values.

However, the problem with this approach arises after you have finished developing your application. Someone else may need to change those values in the future and won’t know where to begin. I suggest that you create a table to your database called SystemTbl. Your users can then access this SystemTbl and modify the data as they see fit.

Setting up the table and the form
After you’ve created the table and added the variables to a new record in the table, you can then create a form for your users that only allows changes, but not additions or deletions. This table should only hold one record for storing this information. Once you’ve stored all your variables, you can use VBA to retrieve those values when your application needs them.

For instance, let’s pretend that your application needs to store the name and phone number of the Application Administrator. As you know, this information should be easily modifiable. There’s no sense in hard-coding it into a form or module.

Your table is predefined with the one record and two fields (at this point):
  • Table: SystemTbl
  • Field1: AdminName
  • Field2: AdminPhone

When you need to display the proper Name and Number on a form for your users, use the On Open or On Load event to populate the labels. In this case, we’ll call the labels “PhoneLabel” and “NameLabel.” Listing A shows the code for our sample function.

Listing A: Our sample function for retrieving system variables
Private Sub SampleForm Form_Open()
On Error GoTo ErrorExit
Dim txtName As String
Dim txtPhone As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SystemTbl", dbOpenSnapshot, dbForwardOnly)
‘—-Test if Recordset has a record.
If rs.EOF Then
 MsgBox "There were no records in your SystemTbl", vbOKOnly, "Error"
 GoTo ExitSub
End If
‘—Test for NULLS in recordset
If IsNull(rs!AdminName) Or IsNull(rs!AdminPhone) Then
 MsgBox "There was no Administrator Information Address Found.", vbOKOnly, "Error"
 GoTo ExitSub
End If
‘—- Grab Recordset Values.
txtName = rs!AdminName
txtPhone = rs!AdminPhone
‘—- Place text onto the form Labels.
Me.NameLabel.Caption = "Administrator Name: " & txtName
Me.PhoneLabel.Caption = "Administrator Phone: " & txtPhone
 Set rs = Nothing
 Set db = Nothing
 Exit Sub
 MsgBox Err.Description
 Resume ExitSub
End Sub

In this function, we have opened the recordset in SnapShot-ForwardOnly. Doing so decreases the amount of resources used when accessing this recordset. We then applied the value of the field in the table to the caption of the Name and Phone Labels.

Finally, we closed out the recordset. Never forget to close out a recordset; it can be accumulated as an unneeded resource that can seriously degrade performance or cause even worse problems. Since there was only one record for your system variables, the values are retrieved lickety-split, and you’ve now incorporated a much more scalable solution to an effective database application.

Todd E. Parker has been a consultant in the IT field for over seven years and is currently employed by Science Applications International Corporation. SAIC is a global IT consulting corporation dealing largely with nuclear and other energy-related companies.

Editor's Picks

Free Newsletters, In your Inbox