Today, I thought we’d take a look at playing around with Database Properties:

• Listing the Properties
• Getting the Value of a Specific Property
• Creating a Property
• Deleting a Property

To simplify my code, I am using my oCurrentDb Self-Healing Object Variable (SHOV).  If you’re not familiar with that yet, then start off by reviewing:

Self-Healing Object Variables

So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables! So What Are Self-Healing Object Variables? In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session.  If reset, they will…

Continue reading →

Or if your truly don’t want to learn/use SHOV, in all the code below, simply replace ‘oCurrentDb’ with ‘CurrentDb’.

Now, let the fun begin!
 

Why Do We Care About Database Properties

Database properties are hidden bits of information about your database’s setup. Some are accessible via the GUI, for instance: Options -> Current Database, while others can be hidden only accessible via code.

Natively, it store information about things like the status bar being displayed or not, should the database compact on close, whether the Shift Bypass is enabled or not, the database version, … information regarding the Navigation Pane, … so on and so forth.

That said, you can add your own properties to store anything you’d like out of plain site:

• Installation Date
• Activation Information
• Format
• … Anything Else You’d Like!

Listing All the Existing Properties and Their Values

Before being in a position to play around with properties, typically, we need to know which one’s already exist. The following will iterate over the existing collection and output each property’s name, type and value in the VBE Immediate Window.

#Const Access_EarlyBind = True
Public Sub ListProperties()
    On Error GoTo Error_Handler
    Dim iCounter              As Long

    Debug.Print "Database Properties"
    On Error Resume Next
    For iCounter = 0 To oCurrentDb.Properties.Count
        Debug.Print , oCurrentDb.Properties(iCounter).Name, oCurrentDb.Properties(iCounter).Type, _
                    oCurrentDb.Properties(iCounter).Value
        ' ***** Use my String_Pad function to make it prettier! (as shown in the commented section below) 
        '       https://www.devhut.net/vba-padding-a-string-to-a-desired-length/
'        Debug.Print , String_Pad(oCurrentDb.Properties(iCounter).Name, 50, PadRight) & _
'                    String_Pad(oCurrentDb.Properties(iCounter).Type, 8, PadRight) & _
'                    oCurrentDb.Properties(iCounter).Value
    Next

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ListProperties" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

By using this Sub you can review the values, make changes to database properties through the GUI and then rerun it to see how the values change. This can facilitate performing updates via code (see below).

You can simply run it in the VBE Immediate Window by simply doing:

ListProperties

which in turn will output something like:

Database Properties
              Name                                              12      C:\Demos\Db Properties.accdb
              Connect                                           12      
              Transactions                                      1       True
              Updatable                                         1       True
              CollatingOrder                                    3       1033
              QueryTimeout                                      3       60
              Version                                           12      12.0
              RecordsAffected                                   4       0
              ReplicaID                                         15      
              DesignMasterID                                    15      
              ANSI Query Mode                                   4       0
              Themed Form Controls                              4       1
              Use Microsoft Access 2007 compatible cache        4       0
              Clear Cache on Close                              4       0
              Never Cache                                       4       0
              AccessVersion                                     10      09.50
              NavPane Category                                  4       0
              Show Navigation Pane Search Bar                   4       0
              Build                                             4       720
              ProjVer                                           3       140
              HasOfflineLists                                   3       70
              UseMDIMode                                        2       0
              ShowDocumentTabs                                  1       True
              Picture Property Storage Format                   4       0
              WebDesignMode                                     2       0
              CheckTruncatedNumFields                           4       1
              Theme Resource Name                               10      Office Theme
              StartUpShowDBWindow                               1       True
              StartUpShowStatusBar                              1       True
              AllowShortcutMenus                                1       True
              AllowFullMenus                                    1       True
              AllowBuiltInToolbars                              1       True
              AllowToolbarChanges                               1       True
              AllowSpecialKeys                                  1       True
              UseAppIconForFrmRpt                               1       False
              AllowDatasheetSchema                              1       True
              DesignWithData                                    1       True
              Show Values Limit                                 4       1000
              Show Values in Indexed                            4       1
              Show Values in Non-Indexed                        4       1
              Show Values in Remote                             4       0
              Auto Compact                                      4       0
              NavPane Closed                                    4       0
              NavPane Width                                     4       218
              NavPane View By                                   4       0
              NavPane Sort By                                   4       1
              AllowBypassKey                                    1       True

Reading a Specific Property Value

The above is great if you want to get a list of all the database properties, but realistically we are typically interested in a specific property. This is where the following function comes in. You need only supply the property name you are interested in and it will return its’ value.

#Const Access_EarlyBind = True
Public Function GetProp(ByVal sPropName As String) As Variant
On Error GoTo Error_Handler

    GetProp = oCurrentDb.Properties(sPropName)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number = 3270 Then
        'Property Not Found
        GetProp = Null
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: GetProp" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

For instance, if we wanted to check the ‘Ribbon Name’ (so the default current database ribbon), we could do:

? GetProp("CustomRibbonID")

which returns:

Rbn_Blank

which is the name of the ribbon set for this database (Options -> Current Database -> Ribbon Name). It will return Null if no Ribbon has been set.

Some might ask:

How did you know to use the term ‘CustomRibbonID’?

That’s where the first sub, from the ‘Listing All the Existing Properties and Their Values’ section, comes in. I ran it, then changed the value, ran it again and was able to determine the property name was ‘CustomRibbonID’ for that GUI element.

Setting a Property Value

So how can we set the value of a Property? Well that is slightly trickier, but still very easy to achieve, by doing something along the lines of:

Public Function SetProp(ByVal sPropName As String, ByVal iType As Integer, ByVal vVal As Variant) As Boolean
On Error GoTo Error_Handler
    #If Access_EarlyBind = True Then
        Dim oProperty         As DAO.Property
    #Else
        Dim oProperty         As Object
    #End If
    
    oCurrentDb.Properties(sPropName) = vVal
    SetProp = True

Error_Handler_Exit:
    On Error Resume Next
    Set oProperty = Nothing
    Exit Function

Error_Handler:
    If Err.Number = 3270 Then
        'Property Not Found, So Create It
        Set oProperty = oCurrentDb.CreateProperty(sPropName, iType, vVal)
        Call oCurrentDb.Properties.Append(oProperty)
        SetProp = True
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: SetProp" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

Notice here the error trapping in the error handler to deal with the case where the property doesn’t exist. First, we need to create it and then add it (append) to the properties collection.

To use this function we can simply doing something like:

? SetProp("CustomRibbonID", 10, "Rbn_Blank") '10 => dbText

So in a single line we can set any property. The above would set the database Ribbon Name (Options -> Current Database -> Ribbon Name) to ‘Rbn_Blank’.

Some might be asking:

How did I know to use 10?

What is 10?

Here’s another example which illustrates how you can disable the Shift Bypass option for your database:

? SetProp("AllowBypassKey", 1, False) 'True to enable it again!

Here are a few more quick examples of it’s usage for creating some custom properties:

? SetProp("InstalledCost", 5, ccur(5.25)) '5 => dbCurrency

? SetProp("InstalledDate", 8, Now()) '8 => dbDate

? SetProp("InstalledGUID", 15, "{DD345678-ABCD-ABCD-0000-DDDDAAAA9012}") ' 15 => GUID

Well, to set a property, we need to supply the type. In this instance, I simply set the property through the GUI and used my ListProperties() to see that the CustomRibbonID property is Type 10 = dbText, so a string value. So, either you know this yourself, or you can set it once manually and extract the Type for use in VBA.

Below is a quick listing of what I perceive the most common Types:

The complete listing is available in the link provided at the end of this article.
 

Deleting a Property

The last aspect of playing around with database properties would be deleting them and once again this is not complicated! We simply do:

Public Function DeleteProp(ByVal sPropName As String) As Boolean
On Error GoTo Error_Handler

    Call oCurrentDb.Properties.Delete(sPropName)
    DeleteProp = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number = 3265 Then
        'Property Not Found in Collection
        DeleteProp = True
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: DeleteProp" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

And can call it like:

? DeleteProp("CustomRibbonID")

Such a command deletes the Ribbon Name property so that no Ribbon is set for the current database.

Download a Demo Database

Feel free to download a 100% unlocked copy by using the link provided below:

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Another Approach

I should also mention that we can Read and Set certain properties using the built-in GetOption() and SetOption() methods.

A quick example to disable the Status Bar.

To read the current value of a property:

? Application.GetOption("Show Status Bar")

To change/set the value of a given property:

Application.SetOption "Show Status Bar", False

Pay Attention

Notice, the Option Names for these methods do NOT correspond to the Property Names that we have previously gotten via VBA! 🤯 You can review the available options by looking over:

Set options from Visual Basic

The value that you pass to the SetOption method as the setting argument depends on which type of option you are setting. The following table establishes some guidelines for setting options.

Registry You Say

A little off-topic, but in the past some have asked about saving information to the Registry. I way write an article on the subject at some point regarding this, but I thought I’d quickly mention that VBA does give us several built-in procedures, that for simple usages, are great for exactly this!

So if you are wanting to store some information in the Registry you should look over:

• GetSetting function
• SaveSetting statement
• DeleteSetting statement

I’ve provided all the necessary links at the end of this article.

Better yet, check out my article on the subject:

Final Remarks

Database properties control some of the look and behavior of your database, ribbon, search pane in the navigation pane, …, but you can go beyond the built-in ones. That’s right, feel free to create your own properties to hold anything you want. Obviously, if it is sensitive information, first encrypt it in some way as it may be hidden, but as we have seen, it remains easy to access.

Also note, that both for VBA and the built-in methods, not all built-in properties seem to work/be editable. For instance, setting any of the Navigation Pane properties, although tested as having been changed, would not be reflected in the GUI, nor when the database was closed and reopened. I have been unable to find any documentation on the matter, so I have reached out to the Access Dev Team on the matter and will post back once they enlighten me.
 

The Story continues

Be sure to check out my detailed property listing in part 2:

Other Resources on the Subject

Document.CreateProperty method (DAO)

Creates a new user-defined Property object (Microsoft Access workspaces only).

Field.Type property (DAO)

Sets or returns a value that indicates the operational type or data type of an object. Read/write Integer.

Application.GetOption method (Access)

The GetOption method returns the current value of an option in the Access Options dialog box, available by choosing the Microsoft Office button, and then choosing Access Options. Variant.

Application.SetOption method (Access)

The SetOption method sets the current value of an option in the Access Options dialog box.

Set options from Visual Basic

The value that you pass to the SetOption method as the setting argument depends on which type of option you are setting. The following table establishes some guidelines for setting options.

GetSetting function (Visual Basic for Applications)

Returns a key setting value from an application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file.

SaveSetting statement (VBA)

Saves or creates an application entry in the application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file.

DeleteSetting statement (VBA)

Deletes a section or key setting from an application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file.