enumerate queries

[Guest]

I'm looking for a method to enumerate all queries and their SQL statements in
an access db.

Essentially, I'm looking to create a table with three columns: autoID,
QueryName, SQL statement.

I imagine this is rather straightforward but I'm not sure how to go about
doing it. As there are several dozen queries in the db I am documenting this
would seem to be a quicker option than manually copying all the queries' SQL
statements into a Word doc.

 

[Guest]

This is untested air code written in haste, so I will not guarantee it, but
it is the basic idea of how to do this.

Dim dbf as DAO.Database
Dim rst as DAO.Recordset
Dim qdfs as QueryDefs
Dim qdf As QueryDef
Dim strSQL As String
Dim strName as String

Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MySQLTable", dbOpenDynaset)
Set qdfs = dbf.QueryDefs
Set qdf = dbf.QueryDef

For each qdf in qdfs
strName = qdf.Name
strSQL = qdf.SQL
With rst
.AddNew
![QueryName] = strName
![QuerySQL] = strSQL
.Update
End With

 

[Guest]

Thanks, this provides some good information.

This is untested air code written in haste, so I will not guarantee it, but
it is the basic idea of how to do this.

Dim dbf as DAO.Database
Dim rst as DAO.Recordset
Dim qdfs as QueryDefs
Dim qdf As QueryDef
Dim strSQL As String
Dim strName as String

Set dbf = Currentdb
Set rst = dbf.OpenRecordset("MySQLTable", dbOpenDynaset)
Set qdfs = dbf.QueryDefs
Set qdf = dbf.QueryDef

For each qdf in qdfs
strName = qdf.Name
strSQL = qdf.SQL
With rst
.AddNew
![QueryName] = strName
![QuerySQL] = strSQL
.Update
End With

I'm looking for a method to enumerate all queries and their SQL statements in
an access db.

Essentially, I'm looking to create a table with three columns: autoID,
QueryName, SQL statement.

I imagine this is rather straightforward but I'm not sure how to go about
doing it. As there are several dozen queries in the db I am documenting this
would seem to be a quicker option than manually copying all the queries' SQL
statements into a Word doc.