HOWTO: Read or Modify User-Defined DAO Properties
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:
- 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.
- 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.
- 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
© Microsoft Corporation 1997, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation
Additional query words:
Keywords : kbprg kbVBp400 kbVBp500 kbhowto APrgDataAcc VB4WIN VBKBDAO VBKBDB VBKBJet VBKBObj vbwin
Version : WINDOWS:2.0,4.0,5.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto