Changing the ODBC Link in VBA Fires the query on more than one DB

[BigOrangeMonkey]

Hi,

I have the following code to change the connection string on a Pass-Through query to point to a different database, when the connection string is changed the query is fired off on both the old connection and the new one, the problem is I am not executing the query!!

i.e.
The query is to Truncate TableA on Database1,
I change the connection to database2,
at the line
"Set catdb.Procedures(strQryName).Command = cmd" where cmd now points to database2
TableA is truncated on Database1 and Database2, Any Ideas why this happens?

Heres the code :

Sub ModifyQuery(strQryName As String, _
                strConnection 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=" & Application.CurrentProject.FullName

   Set cmd = New ADODB.Command
   'Get the query from the Procedures collection.
   Set cmd = catdb.Procedures(strQryName).Command
   ' Update the query's SQL statement.
    With cmd
        .Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
        .Properties("Jet OLEDB:Pass Through Query Connect String") = strConnection
    End With

   'Save the updated query.
   Set catdb.Procedures(strQryName).Command = cmd

   Set catdb = Nothing
End Sub

Thanks,
Steve.

 

[cmmrfrds]

Refresh the procedures collection.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthadorefresh.asp

ADO makes assumptions, and the syntax you are using is suggesting that the query should be executed.

Try this.
Change this
Set catdb.Procedures(strQryName).Command = cmd
To
'Save the updated query.
catdb.Procedures.Refresh