6.7. DAO Object Properties

As you're no doubt already aware from previous chapters, every Access object (like forms and reports) has a collection of properties. In this section, we're going to examine some of those properties, and describe how to use them to change Access and DAO object behavior.

All the properties associated with an Access object exist from the moment you create the object. DAO object properties, however, exhibit quite different behavior. In DAO, depending on the object, not all its properties exist until you set its value. It is quite important, therefore, that you understand the differences between the types of properties used in DAO.

6.7.1. DAO Property Types

In contrast to Access object properties, there are three types of object properties: built-in, system-defined, and user-defined.

• Built-in properties exist when the object is created, and like most of their Access counterparts, define the characteristics of the object itself. For example, Name and Type are examples of built-in properties.

• System-defined properties are those that Access adds to the object's Properties collection when it needs the property in order to work its magic. These are not Jet properties, but are created and used by Access.

• A user-defined property can be added to an object's Properties collection when you explicitly set a value to it. For example, a field's Description property is a user-defined property. Although you can set a value to it when you define the table, Jet doesn't recognize that the property exists until after you've done so. In fact, after you've set its value, it appears in the field's Properties collection, but you still can't see it in the Object Browser, as shown in Figure 6-1.

Figure 6.1. Figure 6-1

6.7.1.1. Creating, Setting, and Retrieving Properties

Without even thinking about it, you've been setting and retrieving properties for as long as you've been programming. Whenever you check the value of a TextBox, or set the Enabled state of a command button, you are working with object properties. In this section, we'll examine how to manipulate Access properties, object properties, and user-defined properties.

You can refer to built-in properties either directly through the object to which they belong or through the object's Properties collection. User-defined properties, on the other hand, do not form part of an object's type library, and thus are not available via that route, so you have to refer to them through the object's Properties collection.

6.7.1.1.1. Setting and Retrieving Built-In Object Properties

The built-in properties that you would be most familiar with are those that affect the way form and report controls work. Even DAO objects have properties that can be manipulated in the same way. For example, to change a TextBox's Enabled property, you can refer to it in either of the following two ways:

Me!TextBox1.Enabled = False
Me!TextBox1.Properties("Enabled")   = False

To check the name of a recordset's Field object, you retrieve its Name property. The following two examples are equivalent ways to check this property:

Debug.Print rst.Fields(0).Name
Debug.Print rst.Fields(0).Properties("Name")

All objects have a default property, which is the property that is referenced when you call the object itself. For example, when you test a Field object directly, you are actually referring to its Value property. The following lines of code all refer to the Field object's Value property:

rst.Fields(0)
rst.Fields(0).Properties("Value")
rst.Fields(0).Properties(0)
rst.Fields(0).Value

6.7.1.1.2. Creating Object Properties

You can only create user-defined properties for persistent DAO objects, such as tables and queries. You can't create properties for nonpersistent objects, like recordsets. To create a user-defined property, you must first create the property, using the Database's CreateProperty method. You then append the property using the Properties collection's Append method. That's all there is to it.

Using the example of a table's Description property, the following code demonstrates just how easy it is:

Public Sub CreateTableProperty(strTableName As String, _
        strFieldName As String, _
        strPropName As String, _
        lngPropType As DataTypeEnum, _
        varValue As Variant, _
        Optional booIsDDL As Boolean = False)

Dim dbs As Database
    Dim prop As Property

    Set dbs = CurrentDb

    'Create the property
    Set prop = dbs.CreateProperty(strPropName, _
lngPropType, varValue, booIsDDL)

    'Append the property to the object Properties collection
    dbs(strTableName)(strFieldName).Properties.Append prop

    Debug.Print
dbs(strTableName)(strFieldName).Properties(strPropName)

    'We don't need the property any more, so delete it
    dbs(strTableName)(strFieldName).Properties.Delete strPropName

    'Clean up
    Set prop = Nothing
    Set dbs = Nothing
End Sub

As a second example, you could even create a special user-defined property for a table in the same way. This approach can be used with all persistent objects.

Public Sub CreateSpecialTableProp(strTableName As String, _
        strPropName As String, _
        lngPropType As DataTypeEnum, _
        varValue As Variant)
    Dim dbs As Database
    Dim prop As Property

    Set dbs = CurrentDb

    'Create the property
    Set prop = dbs.CreateProperty(strPropName, _
        lngPropType, varValue, False)

    'Append the property to the object Properties collection
    dbs(strTableName).Properties.Append prop

    Debug.Print dbs(strTableName).Properties(strPropName)

    'We don't need the property any more, so delete it
    dbs(strTableName).Properties.Delete strPropName

    'Clean up
    Set prop = Nothing
    Set dbs = Nothing
End Sub

6.7.1.1.3. Setting and Retrieving SummaryInfo Properties

As we've already discussed, all objects in Access have properties. Properties come in two flavors: those that change the behavior of the object to which they refer, and those that represent something akin to metadata (information about information, or objects). As you've seen in the preceding sections of this chapter, DAO objects also have properties, and we have already discussed how to set and retrieve those properties. But there is one set of properties that we haven't looked at yet.

When you select Properties from the File menu, Access presents you with the Properties dialog box. This dialog box displays several built-in properties, some you can change, and some you can't. The General tab displays various information about the database, including its file location and size, creation date, and the dates it was last modified and accessed. The Summary tab allows you to enter your own properties, such as the document (database) Title (which is different from the Database Title, which you set from the Tools | Startup menu), Subject, Author, Manager, and so on. These two tabs contain the information the Search facility uses when you want to find a specific file, using File |Open| Find, as shown in Figure 6-2.

Figure 6.2. Figure 6-2

In code, specifically DAO code, you can set and retrieve the value of any of these properties from the SummaryInfo document of the Databases container, for the current database. Of course, you don't have to create these properties before using them. Access creates them automatically when you launch the database. The following example line of code illustrates how to access the Subject property shown in the Properties dialog box.

dbs.Containers("Databases").Documents("SummaryInfo").Properties("Subject")

6.7.1.1.4. Setting and Retrieving User-Defined Properties

You can also create and use user-defined properties for other purposes. We often use a custom database property to record the database version. We like to put it with the other custom properties, so it can be accessed in the two ways described below.

As with the example of a table's Description property, there are two ways to create a user-defined property: using the user interface, and through code.

To create such a property with the user interface, select Properties from the File menu. The Properties dialog box is displayed, as shown in Figure 6-3. Then select the Custom tab. Enter the property name into the Name box, select the appropriate data type, give it a value, and click Add.

Figure 6.3. Figure 6-3

The following example shows how you can create the same property in code, and retrieve its value:

Public Sub SetVersion(strVersion As String)
    Dim prop As Property
    Dim dbs As Database

    'On Error Resume Next

    Set dbs = CurrentDb

    'Set the property's value
    'If it doesn't exist, an error 3270 "Property not found" will occur
    dbs.Containers(1)(3).Properties("Version") = strVersion
    If Err <> 0 Then
        'If the property doesn't exist, create it
        Set prop = dbs.CreateProperty("Version", dbText, strVersion)

        'Append it to the collection
        dbs.Containers(1)(3).Properties.Append prop
    End If

    'Now read the property
    Debug.Print dbs.Containers(1)(3).Properties("Version")

    'Clean up
    Set prop = Nothing
    Set dbs = Nothing
End Sub

First you must test that the property exists. In this example, we test it by attempting to set its value. If all goes well, the property must already exist, and its value is set. If an error occurs, you have to create the property—again by using the CreateProperty method at database level, and then appending it to the appropriate collection.

The Debug.Print line demonstrates how to retrieve the property's value.

Finally, the code cleans up, by destroying all the objects that were instantiated in the procedure.