Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Use Data Access Objects to List Object Names

ACC2000: How to Use Data Access Objects to List Object Names

View products that this article applies to.

This article was previously published under Q210347
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

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.

↑ Back to the top

More information

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To use DAO to list all the objects in your database for the object type that you select, follow these steps:
  1. Start Microsoft Access and open any database.
  2. In the Database window, click Forms, and then click New to create a new, blank form not based on any table.
  3. Add an option group to the form. Set the option group's Name property to ChooseObject.
  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 ChooseObject option group's AfterUpdate property to the following event procedure.
    Sub ChooseObject_AfterUpdate ()

Dim DB As DAO.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.

↑ Back to the top

Keywords: KB210347, kbusage, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 210347
Revision : 2
Created on : 6/28/2004
Published on : 6/28/2004
Exists online : False
Views : 401