Challenge: SQL STMT to Retrieve Saved Query Content (SQL Code)

I

ImJoe

Hello to you all Access gurus out there,

I understand it's easy to list the name of all saved queries of a given
Access database via Msysobjects system table. However, I have not seen any
posting over this NG or other similar ones that also include SQL
statement(content) of these queries, though I've noticed some VB code for
that. Is that because it's simply impossible to get a query content (not
query resultset) from a SQL stmt?

Thanks in advance.
 
O

Ofer

This is an example how to get the SQL of all the queries in the database, I
used the debug.print to display the SQL, you can insert the SQL into a temp
table and then use it, or just to search for a certain string within the SQL

Function GetSQL()
Dim MyDB As Database, MyRec As Recordset, MyQuery As QueryDef
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type=5")
While Not MyRec.EOF
Debug.Print MyRec!Name & "- " & MyDB.QueryDefs(MyRec!Name).SQL
MyRec.MoveNext
Wend
End Function
 
T

Tom Wickerath

In addition to Ofer's answer, you can simply download and install Jeff
Conrad's new documentor tool. One of the options is to report the SQL
statements.

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html


If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello to you all Access gurus out there,

I understand it's easy to list the name of all saved queries of a given
Access database via Msysobjects system table. However, I have not seen any
posting over this NG or other similar ones that also include SQL
statement(content) of these queries, though I've noticed some VB code for
that. Is that because it's simply impossible to get a query content (not
query resultset) from a SQL stmt?

Thanks in advance.
 
Top