Properties Without Fear

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:

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.

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

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:

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.

Name Type Description
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.


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.


Your download file is called PWF.ZIP 
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.