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

 

[CT Witter]

Yes, they are reformatting/changing and then saving as a pass thru query.

 

[mdmilner]

This sounds a little like something I was fiddling with in my spare time a litte while ago. Building my own interface that lets me manipulate fields within a table. I was gunning for the same sort of interface (click and select, move up, move down, change field types) as you'd get from Access itself, I just didn't want the users IN the table itself.

Thinking about it, I'd assume somebody has already built something like this.

Mike