CT Witter
MrExcel MVP
- Joined
- Jul 7, 2002
- Messages
- 1,212
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
Is there a better way to retrieve the SQL of a query?
Thanks,
CT
Code:
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