VBA to Retrieve Query SQL

[CT Witter]

I have the following code to retrieve the SQL from a specified query. The problem is that it can only run once and then locks up the db so I can't run again without closing access.

Is there a better way to retrieve the SQL of a query?

Thanks,
CT

Sub ModifyQuery(strDBPath As String, _
                strQryName As String)
   Dim catDB As ADOX.Catalog
   Dim cmd   As ADODB.Command
   
   Set catDB = New ADOX.Catalog
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath
   
   Set cmd = New ADODB.Command
   ' Get the query from the Procedures collection.
   Set cmd = catDB.Procedures(strQryName).Command
   
   ' Get the query SQL.
   Debug.Print cmd.CommandText
   Application.RefreshDatabaseWindow
   Set cmd = Nothing
   Set catDB = Nothing
   
End Sub

 

[Norie]

Where are you doing this?

In Access or somewhere else?

Sub SpitSQL()
Dim qdf As QueryDef

    For Each qdf In CurrentDb.QueryDefs
        Debug.Print qdf.SQL
    Next
End Sub

 

[CT Witter]

Yes in access, and I need to specify a particular query not all.

 

[Norie]

Well can you not adapt the code I posted?

 

[CT Witter]

No I don't understand the querydef model at all, I've only used ADO

 

[Norie]

Well if you could explain further what you want to do.

The QueryDefs collection is simply a collection of all the QueryDef's in a database.

And a QueryDef is the definition of the query which has the SQL property which represents the SQL string for the query.

It also has a Name property.

 

[CT Witter]

I want to specify a query name ie "Query1" and return the SQL from that query.

I tried using Debug.Print CurrentDb.QueryDefs("Query1").SQL but all I get back is SELECT;

when i do the loop it returns the entire SQL

**EDIT**

Now Seems to work (re-started PC).

Thanks for your help!

 

[mdmilner]

I realize this is an answered question - over and done, but why did you want to get the SQL statement anyways?

Is it for display such as on a form?

 

[CT Witter]

Yep, exactly I am using on a form. I am letting the user view it to make changes and then re-saving.

 

[Norie]

Your letting the user view SQL?