Changing the SQL of an existing query using ADO?

[sjh]

Hi, is it possible to change the SQL of an existing query using ADO? If so, please help me with the syntax!

Or do I have to use DAO QueryDef?

Thank you!

SJH

 

[Golom]

This comes from a MS demo application on converting from DAO to ADO. Its fairly self explanatory.

Sub ADOModifyQuery()
  
   If gbBreakEach Then Stop
   
   Dim cat As New ADOX.Catalog
   Dim cmd As ADODB.Command
   
   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind2002.mdb;"
   
   ' Get the query
   Set cmd = cat.Procedures("Employees by Region").Command
   
   ' Update the SQL
   cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
      "SELECT * FROM Employees WHERE Region = [prmRegion] " & _
      "ORDER BY City"
   
   ' Save the updated query
   Set cat.Procedures("Employees by Region").Command = cmd
   
   Set cat = Nothing

End Sub