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
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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Shaleen Chugh" <shalee...@the-web-works-co.uk> wrote in message
news:uwZFGzV9...@TK2MSFTNGP12.phx.gbl...
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Shaleen Chugh" <shalee...@the-web-works-co.uk> wrote in message
news:uwZFGzV9...@TK2MSFTNGP12.phx.gbl...