get a list of db objects

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I tried to find any information on the subject, but could not.
So, does any of the experts know, how to get a list of tables, queries, etc using VBA
Any help is appreciated

Thanks in advance
Eugene.
 
Using the DAO library, you can get a list of TableDefs and QueryDefs.

In recent versions of Access, you can examine collections such as AllForms
and AllReports of the CurrentProject. In older versions, you can examine the
DAO Documents collections.

Undocumented, but in all versions of Access you can get a list of the
objects by querying the MSysObjects table:


For tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Use type 6 for linked tables, 4 for ODBC linked tables.

Queries: 5
Forms: -32768
Reports: -32764
Modules: -32761
 
Try something along the lines of
Dim cnt As DAO.Container
Dim doc As DAO.Document
Dim dbs As DAO.Database

Set dbs = CurrentDb
For Each cnt In dbs.Containers
Select Case cnt.Name
Case "Tables" ' Tables & Queries
For Each doc In cnt.Documents
Debug.Print "Table/Query - " & doc.Name
Next
Case "Forms"
For Each doc In cnt.Documents
Debug.Print "Form - " & doc.Name
Next
Case "Reports"
For Each doc In cnt.Documents
Debug.Print "Report - " & doc.Name
Next
Case "DataAccessPages"
For Each doc In cnt.Documents
Debug.Print "DataAccessPage - " & doc.Name
Next
Case "Scripts" ' Macros
For Each doc In cnt.Documents
Debug.Print "Macro - " & doc.Name
Next
Case "Modules"
For Each doc In cnt.Documents
Debug.Print "Module - " & doc.Name
Next
End Select
Next

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,

I tried to find any information on the subject, but could not.
So, does any of the experts know, how to get a list of
Click to expand...
tables, queries, etc using VBA?
 
Back
Top