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

Article translations Article translations
Article ID: 210347 - 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).

Expand all | Collapse all

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.

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.

Properties

Article ID: 210347 - Last Review: June 28, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo kbusage KB210347

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com