If you aren’t using DAO’s ability to create new properties, perhaps you should be. Gary outlines a couple of very interesting ways to use this facility to give you more powerful applications.
A simple way to improve your products is by remembering as much as possible about the choices that users made the last time they did something and offering those choices as the default the next time they do the same thing. This information can be stored in a variety of places: INI files, the Registry, or tables in a database. Another convenient storage location can be a database’s DAO Properties collection.
Unfortunately, Access doesn’t make it as easy to use the Properties collection as I’d like. In this article, I’m going to develop some simple “wrapper” functions and subroutines that will let you use properties without fear. I’ll even show a few examples of how you can use these functions to customize your applications. One nice thing about building this layer of insulation between the rest of your application and your property storage is that you can re-implement the wrapper without changing anything else in your code! To illustrate this, I’ll finish the article by showing how to re-write these routines to store the information in a table instead of the Properties collection.
For my purposes, a property is nothing more than a value associated with a name. To make things simpler, I’ll treat all property values as strings. I want to be able to do the following:
- Delete properties
- Add new properties
- Get the value of an existing property (probably with a default value, in case the property doesn’t yet exist)
Once I’ve built routines to do these tasks, I can use these routines wherever I want to make my software smarter.
Properties collection details
Most DAO objects (everything except the Error and Connection objects) have a Properties collection that can be modified. Like all Access collections, the Properties collection has:
- A Count property that shows how many items are in the collection
- An Append method to add objects to the collection
- A Delete method to remove objects
- An Items method to access the collection’s objects, either by name or by position in the collection
In the case of the Properties collection, the members of the collection are Property objects. A Property object has a name, a value, a type, and a flag to indicate whether or not it’s inherited from some other object higher up the hierarchy. I don’t use inheritance, and I’ll only use the string type, so all I care about is the name and value of each property. (You can learn more about a Property object’s properties and what they do in the article “Buy Some Property” in the June 1996 issue of Smart Access.)
The only tricky thing about the Properties collection is that Property objects must exist before they’re referenced. In other words, if the Property named “LastLoginID” doesn’t exist, then trying this will result in an error:
StrLastLogin = CurrentDB.Properties("LastLoginID")
This isn’t a good thing. My wrapper routines hide this behavior and let me use properties without thinking about this sort of messy detail. Listing 1 shows the three routines I developed using the Database object’s DAO Properties collection. (Since what I’m saving isn’t associated with any particular object, it makes the most sense to store the state information in the Properties collection of the Database itself.) As you can see, most of the work is in handling the errors that result from trying to obtain the value of non-existent properties.
Listing 1. The Property wrapper routines.
Const conPropertyNotFound = 3270 Public Sub dsPropertyDelete(strName As String) On Error Resume Next CurrentDB.Properties.Delete strName End Sub Public Function dsPropertyGet(strName As String, _ Optional strDefaultValue As String = "") As String Dim db As Database On Error GoTo MethodError 'Just try to get the property. 'Errors are handled below Set db = CurrentDB dsPropertyGet = db.Properties(strName) Exit Function MethodError: If (Err = conPropertyNotFound) Then 'The error that I expect: Dim prop As Property Set prop = db.CreateProperty(strName, dbText, _ strDefaultValue) db.Properties.Append prop dsPropertyGet = strDefaultValue Else 'Something unanticipated MsgBox Err.Number & " " & Err.Description End If End Function Public Sub dsPropertySet(strName As String, _ strValue As String) Dim db As Database On Error GoTo MethodError 'Just try to set the property. 'Errors are handled below. Set db = CurrentDB db.Properties(strName) = strValue Exit Sub MethodError: If Err = conPropertyNotFound Then Dim prop As Property Set prop = db.CreateProperty(strName, _ dbText, strValue) db.Properties.Append prop Resume Next Else MsgBox Err.Number & " " & Err.Description End If End Sub
Most applications include some sort of About box to display information about the product. It’s very helpful if the About box also provides contact information so that people can get help when they need it. This contact information might change over the life of the product, and it might appear in many places, so I’d rather not embed it in code. What’s more, I want to be able to update this information on the fly, without having to recompile the entire application. Properties make this sort of problem a snap: Simply create ContactName and ContactPhone properties, and use these to customize the message displayed in the About box at runtime. Here’s the code behind my About box form:Properties in use
Private Sub Form_Open(Cancel As Integer) lblContact.Caption = "Please contact " & _ dsPropertyGet("ContactName", "ACME Corporation") & _ " at " & _ dsPropertyGet("ContactPhone", "1-800-BUY-ACME") & _ " if you have " & _ "any questions or require support." End Sub
Another nice touch is to let the users control whether or not they must confirm before exiting the application. If I have a property named ConfirmQuit, then I can write:
Private Sub EndApplication() Dim fQuit as Boolean fQuit = True If dsPropertyGet("ConfirmQuit", True) then FQuit = MsgBox("Are you sure you want to quit?", _ VbYesNo + vbQuestion) = vbYes End if If fQuit then Quit End If End Sub
Of course, you’d also need a form to let users modify the value of the ConfirmQuit property.
There might be times when you want to save state in Properties but don’t want to use the DAO collections. For example, if you’re using the typical Access technique of keeping the data in one MDB and the code in another, then, depending on your upgrade strategy, you might not want to store user settings in the code MDB’s property collection. After all, the settings would be overwritten if your way of updating the code is to do a complete replacement of the code MDB. Since I’ve hidden the details of the properties behind the three routines dsPropertyGet, dsPropertySet, and dsPropertyDelete, I can re-implement them to change the way the information is saved.
To store the properties in tables, I create a properties table, named tblProperties, with the structure shown in Table 1.
Table 1. A table for storing property information.
|Name||Text (50)||The name of the property|
|Value||Text (255)||The value stored in the property|
|Description||Text (255)||Handy place to annotate the property|
|IsViewable||Yes/No||Can the property be seen by anyone?|
|IsEditable||Yes/No||Can the property be modified by anyone?|
The uses of the Name and Value fields are pretty obvious; the rest of the tables can be used to build a simple user interface that will let users modify properties in one central location. Once I have this table, I can put it in the data MDB, add a link to it from the code MDB, and then rewrite my routines. To do this, I first centralized some of the table housekeeping in the FindProperty GetPropertyRecordSet and routines. The first routine just returns a RecordSet for the Properties table; the second tries to find a particular name in this RecordSet. Listing 2 shows the resulting code. Getting a property now means returning the Value field if the name can be found name field, or creating a new record (via dsPropertySet) if the record can’t be found. Setting a property just means making sure that a record with the property Name and Value exists in the RecordSet. This might mean editing an existing record or adding a new one.
Listing 2. The Property wrapper routines re-implemented with tables.
Private Function GetPropertyRecordSet() As Recordset Dim db As Database Set db = CurrentDB Set GetPropertyRecordSet = _ db.OpenRecordset("tblProperties", dbOpenDynaset) End Function Private Function FindProperty(rst As Recordset, _ strName As String) As Boolean 'Returns True if it finds the property 'in the recordset On Error GoTo MethodError FindProperty = False With rst If .EOF Then Exit Function End If .FindFirst "Name = """ & strName & """" FindProperty = Not .NoMatch End With Exit Function MethodError: FindProperty = False Exit Function End Function Public Sub dsPropertyDelete(strName As String) Dim rst As Recordset On Error Resume Next Set rst = GetPropertyRecordSet() If FindProperty(rst, strName) Then rst.Delete End If End Sub Public Function dsPropertyGet(strName As String, _ Optional strDefaultValue As String = "") As String Dim rst As Recordset On Error GoTo MethodError Set rst = GetPropertyRecordSet() If FindProperty(rst, strName) Then dsPropertyGet = rst!Value Else dsPropertySet strName, strDefaultValue dsPropertyGet = strDefaultValue End If Exit Function MethodError: MsgBox Err.Number & " " & Err.Description End Function Public Sub dsPropertySet(strName As String, _ strValue As String) Dim rst As Recordset On Error GoTo MethodError Set rst = GetPropertyRecordSet() With rst If FindProperty(rst, strName) Then .Edit Else .AddNew End If !Name = strName !Value = strValue .Update End With Exit Sub MethodError: MsgBox Err.Number & " " & Err.Description End Sub
Note that the code that the application calls stays exactly the same, because I haven’t changed the way that the application saves, retrieves, or deletes its “properties.” Technically speaking, the interface to the properties has stayed the same; only the implementation has been changed. Now that the state-saving properties of the application are stored in a table in the data MDB, I can update the code MDB without losing this information.
The thrilling concluison
People live in the software that we create. We need to do everything in our power as developers to make our customers’ lives better, happier, and easier. One way to do this is by creating tools that don’t get in the way because they remember how they’ve been used. Access’s Properties collection provides a simple place to store this kind of state information.
The great thing about writing little functions like these is that they insulate the rest of the application from future changes. I could now decide that this state information should be saved for each individual user instead of the application as a whole. I could do this by changing the implementation of the access routines to use a more complicated table structure (with an additional user field), or by using the Windows Registry (under a subkey of HKEY_CURRENT_USER). In every case, the application becomes easier to use — and in the end, that’s what matters.