Built-in vs. Application-Defined Properties

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.”