Microsoft KB Archive/119471

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

Article ID: 119471

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q119471



Moderate: Requires basic macro, coding, and interoperability skills.



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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications for Access 97" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access 97 and 7.0) is called Access Basic in Microsoft Access 2.0 or earlier.



MORE INFORMATION
The following example demonstrates how to use DAO to list all the objects in your database for the object type you select:

 Start Microsoft Access and open any database. Create a new, blank form. 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 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  View the form in Form view. Select the option button for the type of object whose names you want to list.</li></ol>

<div class="references_section">