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


  3. 
    Option Explicit 
  4. Type the following procedure:


  5. 
          '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 
  6. 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.


  7. 
    CreateCustomProp "Language", dbText, "English" 
  8. On the File menu, click Database Properties.


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


  8. 
    Option Explicit 
  9. Type the following procedure:


  10. 
          '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 
  11. 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:


  3. 
          '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 
  4. To test this procedure, type the following line in the Debug window, and then press ENTER:


  5. 
    SetCustomProp "Editor", "Andrew Fuller" 
  6. On the File menu, click Database Properties.


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


Last Reviewed: July 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.