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

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

Post by SW1Kb2 » Mon, 29 Aug 2005 08:02:01


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.
 
 
 

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

Post by T2Zlc » Mon, 29 Aug 2005 08:47:01

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

 
 
 

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

Post by VG9tIFdpY2 » Mon, 29 Aug 2005 18:06:01

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://www.yqcomputer.com/


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.yqcomputer.com/
__________________________________________



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.