HOWTO: Read or Modify User-Defined DAO Properties

Last reviewed: September 29, 1997
Article ID: Q170549
The information in this article applies to:
  • Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0
  • Microsoft Visual Basic Professional and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
  • Microsoft Access versions 2.0, 7.0, 97
  • Microsoft Excel 97 for Windows
  • Microsoft Word 97 for Windows
  • Microsoft PowerPoint 97 for Windows

SUMMARY

This article provides two procedures that allow you to read and modify user- defined properties in a Microsoft Jet database.

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


REFERENCES

Microsoft Access (or Visual Basic) Online Help topics: CreateProperty

(c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation

Keywords          : APrgDataAcc VB4ALL VB4WIN vb5all vb5howto VBKBDAO VBKBDB VBKBJet VBKBObj vbwin GnrlVb kbprg
Technology        : kbvba
Version           : WINDOWS:2.0 4.0 5.0 7.0 97
Platform          : WINDOWS
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.