How To Read or Modify User-Defined DAO Properties (170549)

MORE INFORMATION

The Microsoft Jet database engine supports two types of properties on its objects: intrinsic properties and user-defined properties. Intrinsic properties are always present on an object and easy to use, whereas user- defined properties may not be present all the time.

Normally, a Jet database contains no user-defined properties. However, Microsoft Access will add a number of user-defined properties to any databases it creates, and you may want to read and/or alter these properties.

NOTE:

1. When adding a user-defined property for use with Microsoft Access, you must ensure that both the name and the data type is the same that Microsoft Access uses or Access will ignore the property setting. This is particularly true of numeric types where some are Integer values and others are Byte or Long.
2. The modify routine will add a property if not present or delete a property if a NULL value is passed for the new value. If the property exists, it will ignore the PropType argument (assumes it matches). This may cause an error if you have a property with the same name but a different Type.
3. The routines operate on the Field object and can be easily modified to operate on other database objects.

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

NOTE: (For Access 2.0 developers only.) In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

Function GetFieldProperty(F As Field, _
                             ByVal PropName As String) As Variant
   '
   ' Returns NULL if the property doesn't exist
   '
     On Error Resume Next
     GetFieldProperty = F.Properties(PropName)
   End Function

   Sub ModifyFieldProperty(F As Field, ByVal PropName As String, _
                           ByVal PropType As Long, _
                           ByVal NewVal As Variant)
   Dim P As Property
     On Error Resume Next
     Set P = F.Properties(PropName)
     If Err Then
       '
       ' Add property (as long as NewVal isn't Null)
       '
       If Not IsNull(NewVal) Then
         On Error Goto 0      ' fail if can't add
         Set P = F.CreateProperty(PropName, PropType, NewDesc)
         F.Properties.Append P
       End If
     ElseIf IsNull(NewVal) Then
       '
       ' Delete property
       '
       On Error Goto 0      ' fail if can't delete
       F.Properties.Delete PropName
     Else
       '
       ' Modify property
       '
       On Error Goto 0      ' fail if can't alter
       P.Value = NewDesc
     End If
     Set P = Nothing
   End Sub
				

The code can be called as follows:

   Sub Test()
      Dim db As Database, F As Field
      Dim v As Variant
      v = "This is a description"
      Set db = DBEngine(0).OpenDatabase("NWIND.MDB") ' change name/path
      Set F = db!Employees!Title
      ' Get existing description
      Debug.Print "Existing Title Description is: ";
      Debug.Print GetFieldProperty(F, "Description")
      ' Delete description
      ModifyFieldProperty F, "Description", dbText, v
      Debug.Print "After deleting Description: ";
      Debug.Print GetFieldProperty(F, "Description")
      ' Add description
      ModifyFieldProperty F, "Description", dbText, "Employee's Title"
      Debug.Print "After adding new Description: ";
      Debug.Print GetFieldProperty(F, "Description")
      ' Modify existing title
      ModifyFieldProperty F, "Description", dbText, "Emp Title"
      Debug.Print "After modifying Description: ";
      Debug.Print GetFieldProperty(F, "Description")
      ' Clean-up
      Set F = Nothing
      db.Close
   End Sub