How to read from Access the queries and tables? RESOLVED

[mnavas]

I want to put all the tables and queries names from an Access db to a combo or a list box.

[Bruce Fox]

This may give you a plavce to start (may be other ways).


In Access, goto Menu: Tools > Options (View) and check the System Objects checkbox.

Look at the Tables, you should now see all the System Tables.
One in particular, MSysObjects, holds (amonst other things) ALL the Table Names etc.

Run an SQL that returns 'Name' based on Type 1 items.

(Quries are there to as Type 5)


Bruce.

[Memnoch1207]

To get a list of table names:

SELECT Name FROM MSysObjects WHERE Type = 1;

To get list of query names:

SELECT Name FROM MSysObjects WHERE Type = 5;

additional information:

To get list of Forms:
SELECT Name FROM MsysObjects WHERE Type =-32768; 

To get list of Reports:
SELECT Name FROM MsysObjects WHERE Type = -32764;

To get list of Modules:
SELECT Name FROM MsysObjects WHERE Type = -32761;

To get list of Macros:
SELECT Name FROM MsysObjects WHERE Type = -32766;

hope this helps.

[mnavas]

I found this code.... thanks to all for the help...


Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = OpenDatabase(Data1.DatabaseName)

For Each qdf In db.QueryDefs
' List1.AddItem qdf.Name
List1.AddItem qdf.SQL
Next qdf