How to extract query code from MSysQueries ?

[AJMegaughin]

Does anyone have code which would allow me to reconstruct the SQL code used
in a query from the data stored in the MSysQueries table of an MS Access
database?

I need to document the code I've used in hundreds of queries and would rather
not copy and paste them individually. I've found numerous examples to tell
what the query type is or what tables it uses but nothing to recreate the
actual query code.

AJMegaughin
Edinburgh, Scotland

 

[Krzysztof Naworyta]

AJMegaughin wrote:
| Does anyone have code which would allow me to reconstruct the SQL
| code used in a query from the data stored in the MSysQueries table of
| an MS Access database?
|
| I need to document the code I've used in hundreds of queries and
| would rather not copy and paste them individually. I've found
| numerous examples to tell what the query type is or what tables it
| uses but nothing to recreate the actual query code.


Just use DAO:

Dim db as Database
Dim qr as QueryDef

Set db = CurrentDb

For each qr in db.QueryDefs
debug.print "Query: ", qr.name
debug.print qr.sql
debug.print string(100,"-")
debug.print
Next

 

[John Spencer]

Do you need just the queries you can see in the queries window?

In addition do you need the above plus all the queries you have assigned as
record sources to reports and forms? That is you have not used a stored query
that is in the query window?

In addition do you need the queries you have used as the row source for
combobox and listbox controls?

You can get all those using DAO and some VBA code. Since there are so many I
would probably construct a table to hold the results or export the results to
a text file.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AJMegaughin via AccessMonster.com]

That's great, thanks Krzysztof
I hadn't considered DAO !

AJM