How to use Access custom database properties to store information in a database, out of sight, but available to your code as needed.
Access Archon #152
Introduction
Sometimes you may need to store bits of information in a database, out of sight, but available to your code as needed. This article tells you how to use Access custom database properties for this purpose.
You may be familiar with Word custom document properties. If so, you are in for a surprise when comparing them to Access custom database properties. In Word, you can create custom document properties in the interface, on the Custom tab of a template’s properties sheet, and these properties are listed when you run code to display a document’s custom properties. Likewise, when you create Word custom document properties from code and then open the document’s properties sheet, you will see any properties you created from code in the interface.
Access lets you create custom database properties in the interface, as in Word. But when you run the ListCustomProps1 procedure below to list a database’s custom properties:
Public Function ListCustomProps1()
‘Lists DB properties created in code (as well as built-in properties)
On Error Resume Next
Set dbs = CurrentDb
Debug.Print “Database properties:”
For Each prp In dbs.Properties
Debug.Print vbTab & prp.Name & “: ” & prp.Value
Next prp
End Function
you won’t see any of the properties you created in the interface. Nor will you see the properties (such as Type, Location, Size, and Create Date) on the General page of the properties sheet. Instead, you will see a list like the following in the Immediate window:
Database properties:
Name: E:DocumentsWritingWAWDatabase Properties (AA152).mdb
Connect:
Transactions: True
Updatable: True
CollatingOrder: 1033
QueryTimeout: 60
Version: 4.0
RecordsAffected: 0
ReplicaID:
DesignMasterID:
ANSI Query Mode: 0
Themed Form Controls: 1
AccessVersion: 08.50
Build: 566
ProjVer: 35
5Ads make Office Watch possible, please support our sponsors5Info on advertising click here5
You can create custom database properties from code in Access, using the SetProperty procedure:
Public Sub SetProperty(strName As String, lngType As Long, _
varValue As Variant)
‘Called from various procedures
On Error GoTo ErrorHandler
‘Attempt to set the specified property
Set dbs = CurrentDb
Set prps = dbs.Properties
prps(strName) = varValue
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 3270 Then
‘The property was not found; create it
Set prp = dbs.CreateProperty(Name:=strName, _
Type:=lngType, Value:=varValue)
dbs.Properties.Append prp
Resume Next
Else
MsgBox “Error No: ” & Err.Number _
& “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Sub
After you create some properties from code, you will see them when you run the ListCustomProps1 function listed earlier, along with some standard properties. But you won’t see them in the interface. The properties you create in the interface are added to the Properties collection of a document called “UserDefined” in the Databases container; they can be listed using the following code (note that some built-in properties are also included in this listing):
Public Function ListCustomProps2()
‘Lists DB properties created in the interface (as well as built-in
‘properties)
On Error Resume Next
Dim ctr As DAO.Container
Dim doc As DAO.Document
Set dbs = CurrentDb
Set ctr = dbs.Containers(“Databases”)
Set doc = ctr.Documents(“UserDefined”)
With doc
Debug.Print “Database properties:”
For Each prp In doc.Properties
On Error Resume Next
Debug.Print vbTab & prp.Name & “: ” & prp.Value
Next prp
End With
End Function
The following is a typical set of properties as listed by this procedure (the last one, Test, with the value of “What is this?”, is a custom property created in the interface):
Database properties:
Name: UserDefined
Owner: admin
UserName: admin
Permissions: 0
AllPermissions: 65536
Container: Databases
DateCreated: 10/3/2006 12:06:55 PM
LastUpdated: 10/3/2006 12:06:55 PM
ReplicateProject: True
Test: What is this?
I first noted this peculiar and confusing situation with Access custom properties in my book on the DAO Object Model, written in 1999; the situation is unchanged in Access 2003. Microsoft should rectify this confusing situation so that custom database properties created in code are appended to the same collection as ones created in the interface, and to make the behavior of custom properties consistent among Office applications.
However, there is one benefit to this situation, for Access developers: because custom database properties created in code are not visible in the interface, they are handy places to store odd bits of information you may need to use anywhere in an application, with the assurance that they can’t easily be modified by users.
The GetProperty procedure listed below retrieves the value of a custom Access property. The strDefault argument is useful in case you want to provide a default value if the property has not been set:
Public Function GetProperty(strName As String, strDefault As String) _
As Variant
On Error GoTo ErrorHandler
‘Attempt to get the value of the specified property
Set dbs = CurrentDb
GetProperty = dbs.Properties(strName).Value
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3270 Then
‘The property was not found; use default value
GetProperty = strDefault
Resume Next
Else
MsgBox “Error No: ” & Err.Number _
& “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
References
The code in the sample database needs the following reference (in addition to the default references):
Microsoft DAO 3.6 Object Library
If you import code or objects into a database of your own, you may need to set this reference. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of my Web site. It is accarch152.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Database Properties (AA152).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
