make table query which lists all queries in db and their SQL state

[Guest]

Is there a way to create a query which returns all queries in the db and
their SQL statement? Or is there code that will do this for me?

Essentially, I'm envisioning a table which would have two columns:

Query Name | SQL

(Please don't mention Access' documenter feature as it is poorly designed
for what I'm looking to do.)

Thanks!

Dave

 

[Allen Browne]

You can list the queries with this SQL statement:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

You cannot get the SQL statement of the query like that, unless you write a
VBA function to retrieve it. The function would accept the name of the
query, and return
CurrentDb().QueryDefs(strQueryName).SQL

The simplest way would be to loop through the QueryDefs in code like this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
Debug.Print qdf.Name, qdf.SQL
Next
Set qdf = Nothing
Set db = Nothing