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

MORE INFORMATION

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