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.

Again, differently

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.

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.