Custom DAO Properties

As you know, a property is an attribute of an object. For example, all forms have a Caption property, which defines the text appearing in its title bar. This property can be read and written to, both at design and at run time. Reading the property allows its value to be stored in a variable. Writing to the property allows it to be changed.

Other properties are read-only. For example, the DBEngine object has a Version property which indicates the version number of the DAO interface. This can be inspected, but not changed.

With other properties, whether you can read or write to them depends on whether the object is in Design view, or whether it is being run. For example, the AutoResize property of a form can be read or written to at design time, but is read-only at run time.

A great deal of the time that you spend writing VBA will be spent modifying the in-built properties of Data Access Objects and other objects. But, even with all the versatility provided by these in-built properties, there are times when you would like that little bit more control. This is when you can take advantage of the custom properties which Access exposes.

The simplest way to create a custom property for a Data Access Object is to use the CreateProperty method. We are going to add a custom property to the tblResults table which we created in Chapter 7 and we are going to use the property to store the criteria which were used in the creation of the table.

Try It Out-Creating Custom DAO Properties

1. Open your IceCream.mdb database (if it's not open already) and open the code module behind the form frmCriteria . To do this, open the form in Design view, and then select Code from the View menu.

2. Now find the BuildResultsTable function. This function builds a table of SalesIDs based on the selection made by the user on the form.

3. Add the following declaration to the list of variable declarations at the top of the BuildResultsTable function.

      Dim tdf As TableDef     Dim prpCriteria As Property     Dim intWHEREPos As Integer   

4. Now insert the following lines of code near the end of the function:

    qdfAction.Close   'Now add a custom property to the table to hold the criteria     db.TableDefs.Refresh     Set tdf = db.TableDefs(sTableName)     Set prpCriteria = tdf.CreateProperty("Criteria")     prpCriteria.Type = dbText     iWHEREPos = InStr(sSQL, " WHERE ")     If iWHEREPos = 0 Then     prpCriteria.Value = "All Records"     Else     prpCriteria.Value = Mid$(sSQL, iWHEREPos + 7)     End If     tdf.Properties.Append prpCriteria   BuildResultsTable = True 

5. Now compile the code and close the form, saving the changes you have made.

6. Open the frmCriteria form and specify that you are looking for sales that meet the following criteria:

   

7. When a message box appears, asking you whether you want to see the results, hit the No button.

   

8. Now close the Criteria Selection form and switch to the Immediate window by hitting Ctrl+G . Then evaluate the following statement:

9. ? currentdb .TableDefs("tblResults").Properties("Criteria")

10. The criteria you specified on the Criteria Selection form should now be displayed in the Immediate window (although you will probably notice that the Immediate window doesn't wrap the text for you):

    

How It Works

We created a new property called Criteria for the new table, and used it to store the criteria which was used to generate the table. Let's look at how we did it in a bit more detail.

First, we declare a variable having the data type Property .

 Dim prpCriteria As Property 

Next , we create a reference to the table we have just created.

 Set tdf = db.TableDefs(sTableName) 

Then we create a new property for that object. When we create the property, we need to give it a name . We have called it Criteria .

 Set prpCriteria = tdf.CreateProperty("Criteria") 

The next step is to specify the type of value that the property can hold. We want the new Criteria property to hold a textual value, so we specify the intrinsic constant dbText as the property's type.

 prpCriteria.Type = dbText 

Then, we assign a value to the property. If the SQL string contains no WHERE clause (because no criteria were entered), we set the value of the property to " All Records "; otherwise we set it to the WHERE clause of the SQL string that was passed in.

 intWHEREPos = InStr(sSQL, " WHERE ") If intWHEREPos = 0 Then   prpCriteria.Value = "All Records" Else   prpCriteria.Value = Mid$(sSQL, intWHEREPos + 7) End If 

Finally, we need to make the property persistent . In other words, we need to save it to disk, so it is preserved even when the application is closed. We do this by appending it to the Properties collection of the TableDef object for the table to which it belongs:

 tdf.Properties.Append prpCriteria 

The property can be inspected and set in the same way as any of the in-built properties, either in a procedure or in the Immediate window. So you can display the current value of the table's Criteria property by typing the following in the Immediate window:

 ?currentdb.TableDefs("tblResults").Properties("Criteria") 

In the case where no criteria have been specified, " All Records " will be returned.

Database Properties

Of course, tables aren't the only Data Access Objects which can have custom properties. Some of the most useful applications of custom properties concern the Database object. To start with, let's have a look at how many properties a Database object has by default.

Try It Out-Custom Database properties

1. Create a new Access database and call it DBProperties.mdb .

2. Create a new standard code module (accept the default module name when you save it) in the new database and then, in the References dialog (on the Tools menu in the VBE), make sure that there is a reference to Microsoft DAO 3.6 Object Library . You will need to remove the reference to the Microsoft ActiveX Data Objects 2.1 or 2.5 Library , or else this code will not work.

   

3. In the standard code module you have created, type in the following procedure:

      Sub EnumDBProperties()     Dim pty As Property     Dim strTemp As String     On Error Resume Next     For Each pty In CurrentDb.Properties     strTemp = pty.Name & ": "     strTemp = strTemp & pty.Value     Debug.Print strTemp     Next     End Sub   

4. Run the procedure. In the Immediate window, you should see a list of 14 properties, 10 of which have values displayed. Some of the values (such as the value of the Name property) might be different from the ones shown here, but you should still see 14 properties.

   

5. Now switch to the Database Window and open up the Startup dialog from the Tools menu. Give your application a name of My New Database and hit the OK button.

   

6. Now go back to the Immediate window and delete everything in it. Then run the EnumDBProperties procedure again. Things should look quite a bit different (we've highlighted the new properties that appear this time around).

   

How It Works

The EnumDBProperties procedure displays the properties of the current database. In fact, what we are doing here is looping through the Properties collection of the current Database object, and for every Property object in that collection, we are displaying its Name and Value .

 For Each pty In CurrentDb.Properties     strTemp = pty.Name & ": "     strTemp = strTemp & pty.Value     Debug.Print strTemp   Next 

You may have wondered why we put in the error handling line.

 On Error Resume Next 

The reason for this is that, whereas all Property objects have a Name property, not all have a Value property. Of the 13 standard properties of the Database object, one of them - the Connection property - does not have a Value . If we try to print the Value of a Property which doesn't have one, VBA would normally generate a run-time error. By inserting the statement On Error Resume Next we are telling VBA to ignore any statements that cause errors and simply resume execution on the next line.

But once we make a change in the Startup dialog from the Tools menu, we suddenly find that there are nine new properties. Where did these come from? The answer is that these are application-defined properties . In other words, they are not standard properties that exist in every new database, but are added by Access as needed.

You should also be aware that not all database properties can be accessed using the db.propertyname notation. So although we can do this in the Immediate window:

 ?Currentdb.Version 4.0 

We can't do this:

 ?Currentdb.AppTitle 

In order to determine the value of these non-standard properties, we have to get in through the Properties collection of the Database object. So to find the value of the AppTitle property, we would do this:

 ?Currentdb.Properties("AppTitle").Value My New Database 

This also means that we need to exercise a little care when setting the value of these properties. We need to check that the property exists before we set its value, and if it doesn't exist, we need to create it using the DAO hierarchy as we did earlier. So, if we wanted to programmatically prevent the user from bypassing the Autoexec macro or Startup dialog options, we would set the AllowBypassKey property of the database to False like this:

   Sub KeepEmOut()     Dim db As Database     Dim pty As Property     On Error GoTo KeepEmOut_Err     Set db = CurrentDb     db.Properties("AllowBypassKey").Value = False     KeepEmOut_Exit:     Exit Sub     KeepEmOut_Err:     If Err.Number = 3270 Then       'Error code for "Property not found"...     'so we'll create it ourselves     Set pty = db.CreateProperty("AllowBypassKey", dbBoolean, False)     db.Properties.Append pty     Else     MsgBox Err.Description     Resume KeepEmOut_Exit     End If     End Sub