ACC: Using DAO to Set and Retrieve Custom Database Properties

ID: Q178745

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you click Database Properties on the File menu in Microsoft Access 7.0 or 97, and then click the Custom tab, you can add, delete or modify custom properties of the database. This article describes how to use Data Access Objects (DAO) to set and retrieve the custom properties of a database.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

You can use Data Access Objects (DAO) to set and retrieve the custom properties that are displayed on the Custom tab when you click Database Properties on the File menu. To access these properties programmatically, you must refer to the Properties collection of the UserDefined document object exposed in the Databases container object.

Creating a Custom Property and Setting its Value

To programmatically create a new custom property and set its value, follow these steps:

Open the sample database Northwind.mdb.

Create a module and type the following line in the Declarations section if it is not already there:


Option Explicit

Type the following procedure:


      'The following procedure accepts three arguments: prpName, prpType,
      'and prpValue.
      '
      'prpName: a String value representing the name of the property
      '         you want to create.
      '
      'prpType: an Integer value representing the data type of the
      '         property you want to create. To view valid settings for
      '         this argument, search online help for "Type property,"
      '         display the topic "Type property (DAO)" and note the
      '         constants available for Property objects.
      '
      'prpValue: a Variant value representing the value of the property
      '          you want to create.
      '

      Sub CreateCustomProp(prpName As String, prpType As Integer, _
                           prpValue As Variant)

         Dim db As Database
         Dim doc As Document
         Dim prp As Property

         Set db = CurrentDb
         Set doc = db.Containers!Databases.Documents!UserDefined
         Set prp = doc.CreateProperty()
         With prp
            .Name = prpName
            .Type = prpType
            .Value = prpValue
         End With
         doc.Properties.Append prp

      End Sub

To test this procedure, type the following line in the Debug window, and then press ENTER. To view valid constants that you can use for the prpType argument, search online help for "Type property," display the topic "Type property (DAO)" and note the constants available for Property objects.


CreateCustomProp "Language", dbText, "English"

On the File menu, click Database Properties.

Select the Custom tab.

Note that the Language property has a value of "English" in the properties list.

Retrieving the Value of a Custom Property

To retrieve the value of a custom property, follow these steps:

Open the sample database Northwind.mdb.

On the File menu, click Database Properties.

Select the Custom tab.

From the Name list, select Editor.

In the Value text box, type "Nancy Davolio" (without the quotation marks), and then click Add.

Click OK to close the Properties dialog box.

Create a module and type the following line in the Declarations section if it is not already there:


Option Explicit

Type the following procedure:


      'The following procedure accepts one argument: prpName
      '
      'prpName: a String value representing the name of the property
      '         whose value you want to retrieve.
      '

      Function GetCustomProp(prpName As String) As Variant
         Dim db As Database, prp As Property
         Dim doc As Document

         Set db = CurrentDb
         Set doc = db.Containers!Databases.Documents!UserDefined
         On Error Resume Next
         Set prp = doc.Properties(prpName)
         If Err.Number = 0 Then
            GetCustomProp = prp.Value
         Else
            MsgBox "Property Not Found"
            GetCustomProp = Null
         End If
      End Function

To test this function, type the following line in the Debug window, and then press ENTER:
```
?GetCustomProp("Editor") 
```
Note that "Nancy Davolio" is returned to the Debug window.

Setting the Value of an Existing Custom Property

To set the value of an existing custom property, follow these steps:

Follow steps 1 - 7 in the section "Retrieving the Value of a Custom Property."

Type the following procedure:


      'The following procedure accepts three arguments: prpName, and
      'prpValue.
      '
      'prpName: a String value representing the name of the property
      '         you want to create.
      '
      'prpValue: a Variant value representing the value of the property
      '          you want to set.
      '


   Sub SetCustomProp(prpName As String, prpValue)
      Dim db As Database, doc As Document
      Dim prp As Property

      Set db = CurrentDb
      Set doc = db.Containers!Databases.Documents!UserDefined
      Set prp = doc.Properties(prpName)
      prp.Value = prpValue
   End Sub

To test this procedure, type the following line in the Debug window, and then press ENTER:


SetCustomProp "Editor", "Andrew Fuller"

On the File menu, click Database Properties.

Select the Custom tab.

Note that the Editor property has been changed from "Nancy Davolio" to "Andrew Fuller."

REFERENCES

For more information about using the CreateProperty method, search the Help Index for "CreateProperty method," or ask the Microsoft Access 97 Office Assistant.

For more information about using custom database properties, search the Help Index for "custom properties, database properties," or ask the Microsoft Access 97 Office Assistant.

For more information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: kbmacro vba

Keywords : MdlDao PgmHowto
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto