<

Go Premium for a chance to win a PS4. Enter to Win

x

Access Custom Database Properties

Published on
3,805 Points
805 Views
Last Modified:
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
Comment
0 Comments

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month