Microsoft KB Archive/178745

Knowledge Base

ACC: Using DAO to Set and Retrieve Custom Database Properties

Article ID: 178745

Article Last Modified on 1/22/2007

---

APPLIES TO

• Microsoft Access 95 Standard Edition
• Microsoft Access 97 Standard Edition

---

This article was previously published under Q178745

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. 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:

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:

163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: kbmacro vba

Keywords: kbhowto kbprogramming KB178745

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.