ACC: Using DAO to Set and Retrieve Custom Database Properties

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

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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 engineers 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 the Microsoft fee-based consulting line at (800) 936-5200. 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/refguide/default.asp

Creating a Custom Property and Setting its Value

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

1. Open the sample database Northwind.mdb.

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

         Option Explicit
   

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

4. 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"
   

5. On the File menu, click Database Properties.

6. 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:

1. Open the sample database Northwind.mdb.

2. On the File menu, click Database Properties.

3. Select the Custom tab.

4. From the Name list, select Editor.

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

6. Click OK to close the Properties dialog box.

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

         Option Explicit
   

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

9. 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:

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

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

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

         SetCustomProp "Editor", "Andrew Fuller"
   

4. On the File menu, click Database Properties.

5. 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:

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications