SQL text search.

[lawsonium]

Hi all,

I have done a quick search for this on the forum so I apologise if I have missed the thread that talks about this.

Basically I am interested in doing a text search on the SQL used in my Access queries.

Is this possible and how?

Thanks,

Matt.

 

[gemma-the-husky]

the sql string for a query is the sql property of the querydef object

hence

strsql = currentdb.querydefs("myquery").sql

 

[lawsonium]

Ok, I like this. What would be the best way to loop through all the queries in a database?

Matt.

 

[KeithG]

With a for each loop from the queryDedf collection

 

[Rx_]

Specifically, use this code to find other examples.
This example reqiures the code window to have the Immediate Window open (Control+G in a code module)
The results could be copied and pasted into a text box.
Or,

Sub ListAllQueries() 
  Dim db As DAO.Database 
  Dim queries As DAO.QueryDefs 
  Dim query As DAO.QueryDef 
  Set db = CurrentDb 
  Set queries = db.QueryDefs 
  ' loop through query def collection and print each query's property   
For Each query In queries 
    Debug.Print "Name: " & query.Name & vbCrLf & "SQL: " & query.SQL 
  Next query 
 End Sub

If you feel inventive, take the code from here that list each Linked Table (or linked view) to SQL Server and substitute the QueryDef for the TableDefs.
http://www.btabdevelopment.com/ts/default.aspx?PageId=114

Please mark this as solved if the summary of responses answered your question!
Be sure to give that first responder a well deserved THANKS

 

[spikepl]

Or instead download V-tools