MS Access list of tables,indexes..

[Shaleen Chugh]

Hi,

I would like to find out the tables,idexes,forms,reports using VBA for an MS
access database.Pls let me know if its possible?

Shaleen

 

[Allen Browne]

To list the tables, use this query:

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

For queries, ask for [Type]=5 in the WHERE clause.
Forms: [Type] = -32768
Reports: [Type] = -32764
Modules: [Type] = -32761

For indexes, you will need to loop through the Indexes collection of each
TableDef. The basic idea is:
? Currentdb().TableDefs("MyTable").Indexes.Count

If you need to loop through the fields of each table, see:
http://allenbrowne.com/func-06.html

 

[Douglas J. Steele]

What version of Access?

For 2000 and higher, the CurrentData and CurrentProject objects have a
number of collections in it that will return the tables, forms, reports,
etc.

To list all forms, for example, you can use

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.AllForms
Debug.Print obj.Name
Next obj
End Sub

There are more details at
http://msdn.microsoft.com/library/en-us/vbaac10/html/acobjCurrentData.asp
http://msdn.microsoft.com/library/en-us/vbaac10/html/acobjCurrentProject.asp

For 97 and earlier, you can get the tables from the DAO TableDefs
collection, and the queries from the DAO QueryDefs collection. Forms,
reports and the rest are stored in various Containers.

To list all forms, you'd use

Sub AllForms()
Dim dbCurr As DAO.Database
Dim conForms As DAO.Container
Dim docForm As DAO.Document

Set dbCurr = CurrentDb()
Set conForms = dbCurr.Containers("Forms")
For Each docForm In conForms.Documents
Debug.Print docForm.Name
Next docCurr

End Sub

Indexes are a collection of the TableDef object.