The ValidationRule property, along with other properties such as Size, Required, and DefaultValue, are known as built-in properties.
In addition to these built-in properties, you can specify a variety of other custom properties, which you define yourself, or application-defined properties, which are defined by an application that’s using Microsoft Jet, such as Microsoft Access. For example, when you build a table by using the Microsoft Access user interface, you may want to set the Caption property or Description property for a field. However, because these properties aren’t part of the built-in properties available in a Field object’s Properties collection, DAO may return an error if you try to set these properties from Visual Basic. In the following example, strDbPath
is the path to the database:
Dim dbs As Database Dim tdf As TableDef, fld As Field Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.CreateTableDef("CustomerSurvey") Set fld = tdf.CreateField("ID", dbLong) fld.Attributes = dbAutoIncrField fld.Description = "Survey Identification Number" '<<<Error occurs here. tdf.Fields.Append fld dbs.TableDefs.Append tdf
This code doesn’t execute as written because there is no built-in Description property for a Field object in a Microsoft Jet table. This property is set by Microsoft Access whenever a user enters a field description in a table’s Design view, though it’s not created by default. If you want to use DAO to create a Description property for a field, you must use the CreateProperty method to create the property, and then append the new property to the Properties collection of the field.
The following procedure creates a new table and calls the SetCustomProperty function to set the Description property for a field. The SetCustomProperty function attempts to set the property, and if necessary creates a new Property object and appends it to the Properties collection:
Sub SetFieldDescription() Dim dbs As Database, tdf As TableDef, fld As Field Dim strValue As String, strDbPath As String strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb" ' Return reference to current database. Set dbs = OpenDatabase(strDbPath) ' Create new table. Set tdf = dbs.CreateTableDef("Inventory") Set fld = tdf.CreateField("ID", dbLong) fld.Attributes = dbAutoIncrField tdf.Fields.Append fld dbs.TableDefs.Append tdf strValue = "Survey ID Number" If SetCustomProperty(fld, "Description", dbText, strValue) Then Debug.Print "Property set successfully." Else Debug.Print "Property not set successfully." End If dbs.Close Set dbs = Nothing End Sub Function SetCustomProperty(obj As Object, strName As String, _ intType As Integer, varSetting As Variant) As Boolean Dim prp As Property Const conPropNotFound As Integer = 3270 On Error GoTo Error_SetCustomProperty ' Explicitly refer to Properties collection. obj.Properties(strName) = varSetting obj.Properties.Refresh SetCustomProperty = True Exit_SetCustomProperty: Exit Function Error_SetCustomProperty: If Err = conPropNotFound Then ' Create property, denote type, and set initial value. Set prp = obj.CreateProperty(strName, intType, varSetting) ' Append Property object to Properties collection. obj.Properties.Append prp obj.Properties.Refresh SetCustomProperty = True Else MsgBox "Error: " & Err & vbCrLf & Err.Description SetCustomProperty = False End If Resume Exit_SetCustomProperty End Function
See Also For a complete discussion of built-in, user-defined, and application-defined properties, see “DAO Properties” in Chapter 2, “Introducing Data Access Objects.”