Peter Vogel
Create and use new properties for all your Data Access Objects. In the process, you'll turn up two and a half undocumented recordset properties.
Everything in Access has properties. For example, a control has many properties—including the Name and ControlSource properties—a form has Caption and ViewsAllowed properties, and a table has Names and ValidationText properties. Properties for any object are members of a collection, called the Properties collection, which you can loop through for various purposes. More importantly, you can create your own properties using Data Access Objects (DAO) code. In this article I'll show you how to work with the Properties collection and how to create your own properties.
I'll begin by looking at the properties for a recordset. This routine will create a recordset and loop through its properties, displaying the name of each property in the immediate window:
Sub ListAttributes()
Dim db as Database
Dim rec as Recordset
Dim intProp as Integer
Set db = DBEngine(0)(0)
Set rec = db.OpenRecordset("Select * _
from MyTable")
For intProp = 0 to rec.Properties.Count - 1
Debug.Print rec.Properties(intProp).Name
Next intProp
rec.Close
End Sub
What's interesting about this list is that three undocumented properties, EditMode, AbsolutePosition, and Description, appear in it. (Actually, Description may not appear in the list—more on this later.) You can perform a few tests to find out more about these properties. To work with these properties, you can refer to them by their position in the collection. But, like any other collection, you can also reference members of the Properties collection by name:
Debug.Print rec.Properties("EditMode")
To try setting the value of the EditMode property, you could use this:
rec.Properties("EditMode") = 1
A little experimentation shows that EditMode has three settings: 1 (after the Edit method is used on the recordset), 2 (after the AddNew method), or 0 (all other times). AbsolutePosition reflects the relative record number of the current record (but not for table type recordsets). [Dan Haught discussed the AbsolutePosition property in an article in the January 1996 issue.—Ed.] Both properties are read-only.
The syntax for working with properties may appear odd to you. This is the usual way you reference the EditMode property of the rec recordset:
rec.EditMode
rec.EditMode works, and so does rec.AbsolutePosition, but rec.Description doesn't. While the code will compile, you'll get a "Name not found in this collection" error message when you run the code. To see what's special about this property, you'll have to go one step further and look at the properties of these properties. Don't give up, all this will turn out to be useful by the end of the article.
Members of the Properties collection also have properties, which are also members of a collection, whose members also have properties, which are also members of a collection, and so on. This endless (I assume) regression is made simpler by the fact that there are only four "properties of a property:" Value, Name, Type, and Inherited:
This Inherited property is more interesting than the other three. For a recordset, it turns out that the Description property can be inherited. If the recordset is a dynaset and is based on a single table, Description is inherited and contains the description from the underlying table. However, a dynaset based on multiple tables has no Description property. A table-type recordset has a Description property, but its Inherited property is set to False. As it turns out, this dynamic Description property can't be accessed with the rec.Description syntax, but can be accessed using either the rec.Properties("Description") or rec.Properties!Description syntax. Description is really half undocumented: it's documented, but under the object it's inherited from, the TableDef object.
If Access can add and delete properties with abandon, the question arises: can you? In a true object-oriented environment, you could create a new subclass of an existing class. If you needed your tables to have a property Access didn't provide, you would create a NewTable subclass of Access' Table class. This new class would inherit all the properties of its parent class, plus whatever ones you cared to add.
Well, you can't do that, but you can add new properties to individual Databases, TableDefs, Fields, Indexes, and Queries. Why would you want to? Let me suggest two scenarios:
I can think of three reasons for storing this information in DAO properties rather than in tables. First, information stored in properties is faster to access than table information. Second, properties are less likely to be changed/deleted by accident than tables. But the third reason is the most important to me: it just seems like the right thing to do. Location is a property of the database and should be stored as such.
Adding a property is a two-step process. The first step is to create a property using the CreateProperty method of the object. The second step is to add the property to the appropriate object's properties collection:
Dim db as Database
Dim prp as Property
Set db = DBEngine(0)(0)
Set prp = db.CreateProperty("Location",DB_TEXT, _
"Goderich")
db.Properties.Append prp
The CreateProperty method takes up to three arguments. The first is the name of the property, the second is the datatype, and the third is a value to which you wish to initialize the property. While you can omit the last two arguments, the property object must have its type set before it can be assigned a value, and it must have a value before it can be added to any object. If you omit them when using the CreateProperty method, you'll have to set them in separate statements before appending the property:
Set prp = db.CreateProperty("Location")
prp.Type = DB_TEXT
prp.Value = "Goderich"
db.Properties.Append prp
Changing the value of a property that was previously created is more straightforward:
db.Properties("Location") = "Kitchener"
In the order entry scenario, this is the code to retrieve all the orders for the department specified in the Location property of the database:
Set rec = db.OpenRecordset("Select * From Orders _
Where LocationName = '" & _
db.Properties("Location") & "'")
Here are some notes on user-defined properties:
In the unlikely event someone may be adding or deleting properties while you're working with them, you can use the Refresh method on the Properties collection. This is necessary because Access retrieves all members of the Properties collection (or any other collection) when you first use it. Access doesn't check whether the collection has acquired or lost members, or whether the order of members has changed in the collection, until you do a Refresh. To refresh the Properties collection for the database use this syntax:
db.Properties.Refresh
Say that you'd like to add a VersionNumber property to all the tables in a database. To make that job easier, you may want to create a routine that sets the properties of a table. I've created a function called intSetTableProperty() that does this. I pass a table object, a property name, and a value to this function. intSetTableProperty() attempts to set the property specified to the value that is passed to it. If the property doesn't exist for that table, the function creates the property and initializes it to the passed value. If the property has to be created, the function will set the property type to match the type of the value passed to the routine. intSetTableProperty() returns zero if the property was set and the error code generated if the set fails (see Listing 1).
Using the intSetTableProperty() function, I can use the TableDefs collection to create or reset the VersionNumber property for all the tables in the database:
Dim db as Database Dim intTableDefs as Integer, intError as Integer Set db = DBEngine(0)(0)
For intTableDefs = 0 to db.Tabledefs.Count - 1
intError = intSetTableProperty _
(db.TableDefs(intTableDefs),"VersionNumber",1)
If intError <> 0 Then
MsgBox Error(intError)
End If
Next intTableDefs
The version control system described in scenario two can now use the tables' new version number property to determine whether a table should be upgraded. Once the upgrade is complete, the intSetTableProperty() function would be called again to set the table's version number to the version to which it has been upgraded.
Included on the Companion Disk is PROPUTIL.MDA, an Access 2.0 Add-In that contains a utility called Property Manager (see Figure 1). Property Manager provides a way to explore some of Access' DAO objects and learn more about them. For instance, it turns out that all QueryDefs have the ReturnsRecords property, though the documentation says that only pass-through queries should. With Property Manager you can create your own properties and use those properties to store important information. In addition to letting you review, change, and create properties, Property Manager will allow you to change the name and type of your properties on the fly.
Figure 1. The Property Manager Add-In showing the properties for tblMessages.
Working with the properties collection can only give you a deeper understanding of what makes Access tick. Being able to add your own properties to DAO objects is icing on the cake, enabling you to add capabilities to your objects that weren't there before. All in all, it adds up to more efficient and effective applications.
[The techniques and code presented in this article work in both Access 2 and Access 95, but the Property Manager add-in works only with Access 2.—Ed.]
Listing 1. This function sets a property of a TableDef, creating the property if necessary.
Figure 1. The Property Manager Add-In showing the properties for tblMessages.
Peter Vogel is the applications supervisor at Champion Road Machinery, a Microsoft Certified Solution Developer.
Internet peter.vogel@odyssey.on.ca.
To find out more about Smart Access and Pinnacle Publishing, visit their website at:
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the June 1996 issue of Smart Access. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.