ACC: Listing Object Names Using Data Access Objects (DAO)

Last reviewed: August 29, 1997
Article ID: Q119471
The information in this article applies to:
  • Microsoft Access version 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

In Microsoft Access, you can use data access objects (DAO) to list the objects in your database. This article describes a method using DAO that you can use to display all the objects in your database for the object type you select.

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 the "Building Applications for Access 97" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access 97 and 7.0) is called Access Basic in Microsoft Access 2.0 or earlier.

MORE INFORMATION

The following example demonstrates how to use DAO to list all the objects in your database for the object type you select:

  1. Start Microsoft Access and open any database.

  2. Create a new, blank form.

  3. Add an option group to the form. Set the Option group's Name property to "ChooseObject" (without the quotation marks).

  4. Add seven option buttons with the following properties to the option group:

          Option Button 1:
    
             Name: Tables
             OptionValue: 1
    
          Option Button 2:
             Name: Queries
             OptionValue: 2
    
          Option Button 3:
             Name: Forms
             OptionValue: 3
    
          Option Button 4:
             Name: Reports
             OptionValue: 4
    
          Option Button 5:
             Name: Macros/Scripts
             OptionValue: 5
    
          Option Button 6:
             Name: Modules
             OptionValue: 6
    
          Option Button 7:
             Name: All Objects
             OptionValue: 7
    
    

  5. Set the option group's AfterUpdate property to the following event procedure.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

        Sub ChooseObject_AfterUpdate ()
    

        Dim DB As Database, I As Integer, j As Integer, ok_cancel As Integer
        Dim System_Prefix, Current_TableName, Hidden_Prefix
        Dim Ok as Integer, Cancel as Integer
    

        Ok = 1
        Cancel = 2
        Set db = DbEngine(0)(0)
    

        Select Case Me![ChooseObject]
        Case 1
           'System tables are excluded from the list.
           For I = 0 To db.TableDefs.Count - 1
    
            Current_TableName = db.TableDefs(I).Name
            System_Prefix = Left(Current_TableName, 4)
            Hidden_Prefix = Left(Current_TableName, 1)
            If System_Prefix <>"MSys" And System_Prefix <> "USys" And _
            Hidden_Prefix <> "~" Then
                  ok_cancel = MsgBox(db.TableDefs(I).Name, 65, "TABLE NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
            End If
            Next I
        Case 2
            For I = 0 To db.Querydefs.Count - 1
              ok_cancel = MsgBox(db.Querydefs(I).Name, 65, "QUERY NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
            Next I
        Case 3
            For I = 0 To db.Containers("Forms").Documents.Count - 1
             ok_cancel = MsgBox(db.Containers("Forms").Documents(I).Name, _
             65, "FORM NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
            Next I
        Case 4
            For I = 0 To db.Containers("Reports").Documents.Count - 1
             ok_cancel = MsgBox(db.Containers("Reports").Documents(I).Name, _
             65, "REPORT NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
             Next I
        Case 5
             'Scripts are macros.
             For I = 0 To db.Containers("Scripts").Documents.Count - 1
              ok_cancel = MsgBox(db.Containers("Scripts").Documents(I).Name, _
              65, "MACRO NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
             Next I
        Case 6
             For I = 0 To db.Containers("Modules").Documents.Count - 1
              ok_cancel = MsgBox(db.Containers("Modules").Documents(I).Name, _
              65, "MODULE NAMES")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
             Next I
        Case 7
             For I = 0 To db.Containers.Count - 1
               For j = 0 To db.Containers(I).Documents.Count - 1
                 ok_cancel = MsgBox(db.Containers(I).Name & Chr(13) & Chr(10) _
                 & db.Containers(I).Documents(j).Name, 65, "ALL OBJECTS")
                 If ok_cancel = cancel Then
                  Exit Sub
                 End If
               Next j
             Next I
        End Select
        End Sub
    
    

  6. View the form in Form view. Select the option button for the type of object whose names you want to list.

REFERENCES

Microsoft Access "Building Applications with Microsoft Access 97," Chapter 5, "Working with Objects and Collections"

Keywords          : kbusage PgmHowTo PgmObj MdlDao
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.