Database Properties for Thee

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 TableDefs and 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 Class_Initialize method:

'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.

Image by Peggy und Marco Lachmann-Anke from Pixabay