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

This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

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

Summary
In Microsoft Access, you can use Data Access Objects (DAO) to list theobjects in your database. This article shows you a method that uses DAO todisplay all the objects in your database for the object type that youselect.
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 IntegerDim System_Prefix, Current_TableName, Hidden_PrefixDim Ok as Integer, Cancel as IntegerOk = 1Cancel = 2Set 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 ICase 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 ICase 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 ICase 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 ICase 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 ICase 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 ICase 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 IEnd SelectEnd 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: 01/11/2015 02:05:45 - Revision: 3.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbinfo kbusage KB210347
Feedback