ACC: Using Data Access Objects (DAO) to List Object Names

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 shows you a method that uses DAO to display all the objects in your database for the object type that 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).

   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

   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

   object whose names you want to list.

REFERENCES

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

Additional query words:

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

Last Reviewed: November 21, 1998