Article ID: 170549
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 16-bit Enterprise Edition
- Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
- Microsoft Excel 97 Standard Edition
- Microsoft Word 97 Standard Edition
- Microsoft PowerPoint 97 Standard Edition
This article was previously published under Q170549
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
Keywords: kbhowto kbprogramming KB170549