In my previous post, I talked about saving database properties to a text file to improve our Microsoft Access version control. One of the properties in the sample file I posted was
hg_csv. As you may have guessed, that is not a built-in database property. How did it get there? And why is it there? Glad you asked.
Adding custom database properties
Let's start with how it got there. The
Database shares much in common with other DAO objects, like
QueryDefs. Like those other objects, the
Database object has a Properties collection. You can read through the list of properties as I showed in the post linked above. You can also add your own properties to the object.
Here's the code to add or update a custom property:
Sub SetProp(PropName As String, PropValue As String) Dim db As DAO.Database Set db = CurrentDb On Error Resume Next db.Properties.Refresh db.Properties(PropName) = PropValue If Err.Number = 3270 Then 'Property does not exist Dim Prop As DAO.Property Set Prop = db.CreateProperty(PropName, dbText, PropValue) db.Properties.Append Prop End If End Sub
Of course, we also need a routine to retrieve the properties:
Function GetProp(PropName As String, _ Optional DefaultValue As String = "") As String If Len(DefaultValue) > 0 Then On Error Resume Next GetProp = DefaultValue End If GetProp = CurrentDb.Properties(PropName) End Function
Note that I intentionally placed the
On Error Resume Next line inside the
If statement. If I pass a default value to the function and the property does not exist, the function returns the default value. If I don't pass a default value and the property does not exist, the function raises an error.
Why do I do it this way? Because, as a programmer, if I pass a default value to the function I'm signaling that I expect the property may not exist. If I don't pass a default value, that implies that I expect the property will exist. And if it does not, that is an invalid program state which should raise an error and let it bubble up to the calling procedure.
Getting and setting properties via Property Get/Let
In my own programs, I actually have a singleton class module where I get and set database property values (among many other things). Here is an excerpt from that class module. Note that
m_objDB is a class-level database object that is opened in the
'Purpose: Retrieves a property value stored in the front-end database itself. 'Notes - If the property has not been set, the DefaultValue is returned instead ' - However, if no DefaultValue is set either, an error is raised (3270: Property not found) ' - There are certain property names with special meaning that should not be used for other purposes ' Reserved property names: ' - PgmName: the friendly name of the program; if not set, the front-end's filename without extension is used ' - hg_csv: comma delimited list of tables whose data should be exported by decompose.vbs Public Property Get Prop(PropName As String, _ Optional DefaultValue As String = "") As String m_objDB.Properties.Refresh If Len(DefaultValue) > 0 Then On Error Resume Next Prop = DefaultValue End If Prop = m_objDB.Properties(PropName) End Property Public Property Let Prop(PropName As String, _ Optional DefaultValue As String = "", _ ByVal sProp As String) Dim P As DAO.Property On Error Resume Next m_objDB.Properties.Refresh m_objDB.Properties(PropName) = sProp If Err.Number = 3270 Then 'Property does not exist Set P = m_objDB.CreateProperty(PropName, dbText, sProp) db.Properties.Append P End If End Property
Why are we doing this?
I planned on answering that question in this post, but I'm running long on words and short on time. So I'll answer the Why in a future post.