Microsoft KB Archive/210347

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

Article ID: 210347

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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:  Start Microsoft Access and open any database. In the Database window, click Forms, and then click New to create a new, blank form not based on any table. Add an option group to the form. Set the option group's Name property to ChooseObject. 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

  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  View the form in Form view. Select the option button for the type of object whose names you want to list.</li></ol>

Keywords: kbhowto kbinfo kbusage KB210347

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.