The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Where to Store Those Pesky One-Off Values – Use An Admin Table

Introduction

When you are developing a database application, most of the things you need to track fall into neat, clearly defined categories. You will store that data in dedicated tables and will know exactly where to look for it.  Sometimes you might have a value that has to be stored only once and it never changes, the application name for example. Something of this nature could easily be recorded as a constant value in your VBA code.
 
Along the way though, there are invariably some little pieces of data or information that you need to hold onto for only a short time, the last selection a user made for example. Where do you put these miscellaneous values?
 
This month, I will introduce a method to hold onto these pesky, but important pieces of data that your application will need to do its work: An Admin Table.
 

Alternatives

Global Variables
Some Access developers will store one-off items in global variables of their VBA code. I have watched developers get into heated battles about this practice. There are two main downsides to this approach.
 
First, it requires a good naming convention. With this method, understanding what the variables do becomes much easier. However, if you have a lot of one-off data points to record, it can be difficult to keep track of their names and values.
 
The second problem with variables is that, if your code hits an error that it can’t handle, the variable will be lost, which may require the user to restart the application.
 
TempVars
TempVars were introduced with Access 2007, and they are a magnificent tool. Regardless of whether your code ends abruptly or not, the value of the TempVar is preserved.
 
The main downside of TempVars, though, is that they do not persist from session to session. For example, if you would like to help your user continue working on a record that he or she used in the last session, the TempVars are not able to do this.
 

The Solution – An Admin Table

With an admin table, you can store virtually any kind of variable on-the-fly that will be preserved until you change it. This provides persistence not only if the code crashes, but also from session to session.
 
If you are using a split database (see the March 2008 Wizard for more info about this), the admin table should exist in the front end so that it is unique to each user.
 
The structure for the table I use is shown below.



As you can see, it is a fairly simple table. The first field is simply an identifying key, the second and third fields are text fields, and the last field is a memo field used for notes longer than 255 characters.
 
Using this structure, I have the ability to store not only text values but also date and numeric values.
 
Although the value field is set up as text, nothing prevents it from storing other types of values. I will go into this in more detail below when I review how to retrieve values from the admin table.

Storing Values
In order to store a value in the admin table, a record must already exist. To add a new record is simple, open the table and add a new row with the name of value to be stored in the fldItem field. Note that this is a one-time only process for any new item, a user will never add an item to the admin table; this is a developer only process.
 
Once a row is in place, it is simply a matter of calling the code UpdateAdminTable (see below).
 
In this code, I am using a record set and moving to the first record that meets the criteria where the field item equals the name of the item being stored. And then it simply edits the contents of the fldValue field.
 
I use the record set and edit approach rather than using a SQL update statement because it avoids the problem of apostrophes within the value you are storing.
 
Sub UpdateAdminTable(strItem As String, strValue As String,)
    Dim db As dao.Database
    Dim rst As dao.Recordset
    Dim strSQL As String
    Dim strTbl As String
   
    Set db = CurrentDb
   strSQL = "SELECT * FROM zhtblAdmin WHERE (((" & _
             "fldItem)='" & strItem & "'));"
   
    Set rst = db.OpenRecordset(strSQL)
    With rst
        If Not (.EOF And .BOF) Then
            .MoveFirst
            .Edit
                    !fldValue = strValue & ""
            .Update
            Else
            .AddNew
              !fldItem = strItem
              !fldValue = strValue & ""
            .Update
        End If
    .Close
    End With
   
    Set rst = Nothing
    Set db = Nothing
End Sub
 
You will note that the routine takes two inputs, one for the name of the item being stored and the second for the value being stored.
 
Retrieving Values
To retrieve an item from the table, I use the simple lookup statement below, substituting in the actual name of the item to be retrieved.
 
DLookup("fldValue", "zhtbladmin", "fldItem = ‘NameofItemToBeRetrieved’")
 
If needed, I can use a conversion wrapper to get to a date cdate() or numerical value cdbl(). If this is a bit Greek to you, see the Tip of the Month in this issue.
 
Using this approach, any of those one-off values is easy to store and to retrieve; and it has the extra added attraction of persisting from session to session.
 

Conclusion 

In this month’s Wizard, I showed you how to use a dedicated admin table to store and retrieve one-off values. This now becomes a convenient landing spot for any of the miscellaneous pieces of information you might need to keep track of in your application.  Those pesky one-off items are no longer pesky, but truly simple managed.

Tip of the Month: Conversion Functions

In this month’s Wizard, I referenced a conversion function to take a value from a text state to a date or a double state.
 
There are limitations to this. If you are converting the string 2/14/2012 to a date, you must ensure that you have a valid string to represent the date before conversion. The same holds true for numeric values.
 
In many instances, the VBA engine within Access will handle this for you without the explicit conversion; however, I recommend explicit conversion to reduce the possibility of misunderstanding exactly how a value makes the transition from a text value to another type of value.
 
To get a list of the types of conversions available, check out "conversion functions" in the online Access help.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886