ACC: Listing Object Names Using Data Access Objects (DAO)
ID: Q119471
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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.
REFERENCES
Microsoft Access "Building Applications with Microsoft Access 97," Chapter
5, "Working with Objects and Collections"
Additional query words:
Keywords : kbusage MdlDao PgmObj
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbinfo
|