Access Custom Database Properties

Helen Feddema
CERTIFIED EXPERT
Published:
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.

Variables (both private and public) certainly have their uses, but if you need to store some data in a way that persists through database closing and reopening, custom database properties are the way to go.  Values stored in these properties can be used in forms, queries and reports, as well as in code.


While you're working through the article you can refer to this sample database to see the variables in action: Custom Properties.mdb


Creating a Property

You can create custom database properties in an Access database by selecting File => Info => "View and edit database properties" (see screenshot below) to open the Properties dialog, unchanged for many versions now regardless of cosmetic changes to Access.



Click the Custom tab of the dialog to see the custom properties; this dialog works much the same as the dialog for Word document properties, at least for properties you create in the interface:



Helpful Tip: As with Word doc properties, there is a trick to adding a new custom property of the Text type -- you can't add a property with no value, so you need to enter (or set in code) a single space when adding a new Text type property, unless you actually need a specific default value for it.  


This dialog is useful for entering miscellaneous information that you might want to modify in the interface, but it is generally more useful to create and edit custom database properties in VBA code.  


Creating Properties in VBA

Database properties created in code can't be viewed in the Properties dialog, which is either an annoying bug or a useful security feature, depending on how you look at it.  The code listed below can be saved as a standard module (or you can import the procedures into an existing module); it has procedures for creating and retrieving data stored in custom properties, as well as listing them to the Immediate Window. 


Option Compare Database
Option Explicit


Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties

Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'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)
      CurrentDb.Properties.Append prp
      Resume Next
   Else
    MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
End Sub


Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Called from various procedures
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 _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
End Function


Public Function ListCustomProps()
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Lists custom database properties
On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Custom database properties:"
   
   For Each prp In _
      dbs.Containers("Databases").Documents("UserDefined").Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp
End Function


Public Function ListAllProps()
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Lists all database properties
On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "All database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp
End Function


Getting a List of Properties

There are two procedures for listing properties in the module: ListCustomProps and ListAllProps.


It would be nice if you could get a list of just your custom properties and their values, but unfortunately that is not the case -- neither of these procedures yields a list of just the custom properties you have created in code.  However, you can see your properties (among a long list of other properties) by using the ListAllProps procedure.  


The ListCustomProps procedure lists a few built-in properties, as well as any custom properties created in the interface:


Custom database properties:
   Name: UserDefined
   Owner: admin
   UserName: admin
   Permissions: 0
   AllPermissions: 65536
   Container: Databases
   DateCreated: 3/31/2017 11:03:59 AM
   LastUpdated: 3/31/2017 11:03:59 AM
   ReplicateProject: True



If you run the ListAllProps procedure, you will get a much longer list, which (curiously) lists many custom properties created by one or more add-ins that I use, as well as custom properties created in code, and built-in properties:


All database properties:
   Name: \\OPTIPLEX-HOME\Users\Helen Feddema Home\Documents\ExpertsExchange\Article Images\Custom Properties.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
   NavPane Category: 0
   Show Navigation Pane Search Bar: 1
   UseMDIMode: 1
   ShowDocumentTabs: True
   Build: 1162
   CheckTruncatedNumFields: 1
   AppTitle: Custom Properties Demo
   StartUpShowDBWindow: True
   StartUpShowStatusBar: True
   AllowShortcutMenus: True
   AllowFullMenus: True
   AllowBuiltInToolbars: True
   AllowToolbarChanges: True
   AllowSpecialKeys: True
   UseAppIconForFrmRpt: False
   AllowDatasheetSchema: True
   WebDesignMode: 0
   DesignWithData: True
   Show Values Limit: 1000
   Show Values in Indexed: 1
   Show Values in Non-Indexed: 1
   Show Values in Remote: 0
   Auto Compact: 0
   Track Name AutoCorrect Info: 0
   Picture Property Storage Format: 1
   ProjVer: 119
   StartUpForm: frmCustomProperties
   ExclusionChoice: 3
   BackupSuffix: _copy
   BackupChoice: 2
   BackupPrefix: _
   PrefixOrSuffix: 2
   DateFormat: d-mmm-yyyy
   NavPane Closed: 0
   NavPane Width: 316
   NavPane View By: 0
   NavPane Sort By: 1
   FrontEndSaveNo: 2
   FrontEndSaveDate: 3/31/2017
   FolderPath: C:\Users\Helen Feddema\Documents\Address Book
   BooleanValue: False
   LongValue: 4523
   CurrencyValue: 1500
   TextValue: Next meeting is in August 2017
   IntegerValue: 14
   SingleValue: 2545.259
   DoubleValue: 14552.36
   DateValue: 3/22/2017



A Custom Properties Demo

To demonstrate the use of Access custom database properties, I made an unbound form with controls for entering and displaying custom properties of various data types.



Each data type has a control for selecting or entering a value, which is saved from the AfterUpdate event of the control to a custom property of the appropriate data type.  The form's Load event retrieves the values and writes them to the controls.


The Select Folder Path command button uses an Office FileDialog FolderPicker dialog to let you select a folder, whose path is then saved to the FolderPath custom property.  This is very handy when you need to use a folder path in code, but don't want to hard-code it.  Once the path is selected, it can be retrieved when needed anywhere in your code.  You only need to re-select the path if you decide that you need another folder.


The demo form's code is listed below:


Option Compare Database
Option Explicit


Private blnValue As Boolean
Private curValue As Currency
Private dblValue As Double
Private dteValue As Date
Private intValue As Integer
Private lngDataType As Long
Private lngValue As Currency
Private sglValue As Single
Private strFolderPath As String
Private strPrompt As String
Private strPropertyName As String
Private strPropertyValue As String
Private strText As String
Private strTitle As String

Private Sub chkBooleanValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   blnValue = Nz(Me![chkBooleanValue].Value, False)
   strPropertyName = "BooleanValue"
   lngDataType = dbBoolean
   Call SetProperty(strPropertyName, lngDataType, blnValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub cmdSelectFolderPath_Click()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   Dim fd As Office.FileDialog
   Dim txt As Access.TextBox
   
   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   Set txt = Me![txtFolderPath]
   strPropertyName = "FolderPath"
   
   With fd
      .Title = "Browse for folder"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         strPropertyValue = CStr(fd.SelectedItems.Item(1))
         Debug.Print "Property value: " & strPropertyValue
         lngDataType = dbText
         Call SetProperty(strPropertyName, lngDataType, _
            strPropertyValue)
         txt.Value = strPropertyValue
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub Form_Load()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error Resume Next
   DoCmd.RunCommand acCmdSizeToFitForm
On Error GoTo ErrorHandler
   
   'Load control values from custom properties
   strFolderPath = Nz(GetProperty("FolderPath", ""))
   Me![txtFolderPath].Value = strFolderPath
   strText = Nz(GetProperty("TextValue", ""))
   Me![txtTextValue].Value = strText
   curValue = Nz(GetProperty("CurrencyValue", 0))
   Me![txtCurrencyValue].Value = curValue
   dteValue = Nz(GetProperty("DateValue", Date))
   Me![txtDateValue].Value = dteValue
   intValue = Nz(GetProperty("IntegerValue", 0))
   Me![txtIntegerValue].Value = intValue
   lngValue = Nz(GetProperty("LongValue", 0))
   Me![txtLongValue].Value = lngValue
   blnValue = Nz(GetProperty("BooleanValue", False))
   Me![chkBooleanValue].Value = blnValue
   dblValue = Nz(GetProperty("DoubleValue", 0))
   Me![txtDoubleValue].Value = dblValue
   sglValue = Nz(GetProperty("SingleValue", 0))
   Me![txtSingleValue].Value = sglValue
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.Name & " Form_Load procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtCurrencyValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   curValue = Nz(Me![txtCurrencyValue].Value, 0)
   strPropertyName = "CurrencyValue"
   lngDataType = dbCurrency
   Call SetProperty(strPropertyName, lngDataType, curValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtDateValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   If IsDate(Me![txtDateValue].Value) = False Then
      strTitle = "Invalid date"
      strPrompt = "Please enter a valid start date"
      Me![txtDateValue].SetFocus
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      dteValue = Nz(Me![txtDateValue].Value, Date)
   End If
   
   strPropertyName = "DateValue"
   lngDataType = dbDate
   Call SetProperty(strPropertyName, lngDataType, dteValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtDoubleValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   dblValue = Nz(Me![txtDoubleValue].Value, 0)
   strPropertyName = "DoubleValue"
   lngDataType = dbDouble
   Call SetProperty(strPropertyName, lngDataType, dblValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtIntegerValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   intValue = Nz(Me![txtIntegerValue].Value, 0)
   strPropertyName = "IntegerValue"
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, intValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtLongValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   lngValue = Nz(Me![txtLongValue].Value, 0)
   strPropertyName = "LongValue"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtSingleValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   sglValue = Nz(Me![txtSingleValue].Value, 0)
   strPropertyName = "SingleValue"
   lngDataType = dbSingle
   Call SetProperty(strPropertyName, lngDataType, sglValue)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Private Sub txtTextValue_AfterUpdate()
'Created by Helen Feddema 31-Mar-2017
'Last modified by Helen Feddema 31-Mar-2017


On Error GoTo ErrorHandler
   strText = Nz(Me![txtTextValue].Value, " ")
   strPropertyName = "TextValue"
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, strText)
   
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub


Custom database properties take a bit more time to set up than the alternatives (variables and form control references), but they save lots of time that would otherwise be needed to revise hard-coded paths, or make sure that a form is open so a reference to a control on that form works.  With database properties, once they are set, they are available for use at any time, and they never disappear.  I think you will find that this technique both saves time and gives you more control over how your application works.

0
11,857 Views
Helen Feddema
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community