Return objects

[tcb]

How do I use this code which was originally posted by Douglas Steele
(link below) to loop through the objects of a db that is not the
current db, e.g. "C:\Another.mdb"

Sub AllForms()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllQueries
Debug.Print obj.Name
Next obj
End Sub

http://groups.google.com/group/micr...p+through+tables+msys&rnum=3#f56badf2ed689cc3

 

[Douglas J. Steele]

Try

Sub AllForms()
Dim obj As AccessObject, dbs As Object, app As Object
Set app = CreateObject("Access.Application")
Set dbs = app.OpenCurrentDatabase("C:\Another.mdb")
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllQueries
Debug.Print obj.Name
Next obj
app.CloseCurrentDatabase
Set app = Nothing
End Sub

 

[tcb]

I get a runtime error 424, Object Required at this line:

 

[Douglas J. Steele]

Sorry, I only have Access 97 installed on this machine, so I couldn't
test...

Let's try a completely different tack:

Sub ListForms()
Dim db As DAO.Database
Dim ctr As Container
Dim doc As Document
Dim frm As Form

Set db = OpenDatabase("C:\Another.mdb", False, True)
Set ctr = db.Containers!Forms
For Each doc In ctr.Documents
Debug.Print doc.Name
Next doc
Set db = Nothing

End Sub

 

[tcb]

Would there be a way, using your first tack?

If I do this below, it also returns query names. I would like to limit
to returning table names.

Sub ListTables()
Dim db As DAO.Database
Dim ctr As Container
Dim doc As Document
Dim frm As Form

Set db =
OpenDatabase("C:\DATABASE_DESIGN\HHH_GPO\HHH_AppendQueries\Misc\HGPO_data_2006-10-14_1043.mdb",
False, True)
Set ctr = db.Containers!Tables
For Each doc In ctr.Documents
Debug.Print doc.Name
' TableInfo (doc.Name)
Next doc
Set db = Nothing

End Sub

 

[Douglas J. Steele]

You don't need to go the Containers route for tables or queries: just for
Forms, Reports, Modules and Macros.

Sub ListTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef

Set db =
OpenDatabase("C:\DATABASE_DESIGN\HHH_GPO\HHH_AppendQueries\Misc\HGPO_data_2006-10-14_1043.mdb",
False, True)
For Each tdf In db.TableDefs
Debug.Print tdf.Name
Next tdf

For Each qdf In db.QueryDefs
Debug.Print qdf.Name
Next tdf

db.Close
Set db = Nothing

End Sub

Note that this is going to give you EVERYTHING (although so, too, will the
Container approach). You're going to get the System Tables (they all start
MSys) in the first loop, and all of the queries related to SQL statements
that have been provided as the RecordSource for forms or reports, or as the
RowSource for combo boxes or list boxes on forms.

 

[tcb]

Good! Thank you.