Access Cookbook - Ken Getz [161]
Append the new property to the Properties collection of the host object. For example, to add a Description property to the current database, you might write code like this:
Dim db As DAO.Database
Dim prp As Property
Set db = CurrentDb( )
' Step 1
Set prp = db.CreateProperty( )
' Step 2
prp.Name = "Description"
prp.Type = dbText
prp.Value = "Sample Database"
' Step 3
db.Properties.Append prp
To combine Steps 1 and 2, you could set the properties of the new property at the time you create it:
' Steps 1 and 2
Set prp = db.CreateProperty("Description", dbText, "Sample Database")
' Step 3
db.Properties.Append prp
Once you've followed these steps, you should be able to retrieve the database's Description property with a statement like this (note that you must use the explicit reference to the Properties collection in this case, because Description is a user-defined property):
Debug.Print CurrentDb.Properties!Description
To relieve you from worrying about the differences between user-defined and built-in properties and whether or not a property already exists for a given object, we've provided the acbGetProperty and acbSetProperty functions.
The acbGetProperty function is the simpler of the two: it attempts to retrieve the requested property. acbGetProperty may fail for two reasons: the object itself doesn't exist, or the property you've tried to retrieve doesn't exist (errors acbcErrNotInCollection and acbcErrPropertyNotFound, respectively). If either of these errors occurs, the function returns Null. If any other error occurs, the function alerts you with a message box before returning Null. If no error occurs, the function returns the value of the requested property. For an example of calling acbGetProperty, see Recipe 7.9.2 and 07-09.MDB.
The source code for acbGetProperty is:
Public Function acbGetProperty(obj As Object, _
strProperty As String) As Variant
' Retrieve property for an object.
' Return the value if found, or Null if not.
On Error GoTo HandleErr
acbGetProperty = obj.Properties(strProperty)
ExitHere:
Exit Function
HandleErr:
Select Case Err.Number
Case 3265, 3270 ' Not in collection, not found.
' Do nothing!
Case Else
MsgBox Err.Number & ": " & Err.Description, , "acbGetProperty"
End Select
acbGetProperty = Null
Resume ExitHere
End Function
The acbSetProperty function is more interesting. It attempts to set the value of the property you pass to it. This function has several interesting characteristics:
If you ask it to set a property that doesn't currently exist, it attempts to create that property and then sets its value.
The data type is declared optional, using the DataTypeEnum enumerated type, with dbText as the default value. If you don't tell it what the data type of the new property is to be (i.e., if you leave that parameter blank), the code will use the dbText type by default.
The function returns the old value of the property, if there was one, so you can store it away and perhaps reset it once you're done with your application.
To make sure the code will work with either user-defined or built-in properties, the code uses an explicit reference to the Properties collection.
To tell if it needs to try to create the property, the function traps the acbcErrPropertyNotFound error condition (error 3270); if that error occurs, it uses the CreateProperty method to try to create the necessary property.
If you try to assign an invalid property value, Access triggers the acbcErrDataTypeConversion error condition (error 3421). In that case, there's not much acbSetProperty can do besides alerting you to that fact and returning Null.
The source code for acbSetProperty is:
Public Function acbSetProperty( _
obj As Object, strProperty As String, varValue As Variant, _
Optional propType As DataTypeEnum = dbText)
' Set the value of a property.
On Error GoTo HandleErr
Dim varOldValue As Variant
' This'll fail if the property doesn't exist.
varOldValue = obj.Properties(strProperty)
obj.Properties(strProperty)