ACC2000: How to Use Data Access Objects to List Object Names (210347)

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.