Passing parameter in pass-through query using ADO

[Danman75]

I know how to do this using DAO but I need to use ADO instead so I can keep my programming uniform. In DAO you can change the SQL in a query using recordset and querydef but I can't figure out how to do this in ADO. The SQL statement in my pass_thru query is: EXEC sp_RefusedSales.

 

[jiqjaq]

It's done the same way in ADO, by changing the SQL in the query. In DAO you change the string that's in the querydef. In ADO you change the string that get's passed for the recordset .Open or .Execute, or for a command object the .commandtext .

You pass the parameters to "EXEC sp_RefusedSales" by using "EXEC sp_RefusedSales " & "Param1" & "," & "Param2" .

 

[Danman75]

Please provide me with more detail. In DAO, I know to use .SQL (Ex. rst.SQL = "Exec sp_RefusedSales" & ParamString) to go inside the query to change the SQL statement but that syntax doesn't work in ADO. Please help!

 

[jiqjaq]

I'm not sure what it is that you don't understand...

Dim con as ADODB.Connection
Dim rsADO as ADODB.Recordset
DIm cmd as ADODB.Command

SET con = New ADODB.Connection
SET rsADO = New ADODB.Recordset
-define connection (& optionally any command) parameters here
con.Open

'Using command object - see Help topic: Execute Method (ADO Command)
For a row-returning Command:
Set rsADO = cmd.Execute( RecordsAffected, Parameters, Options )

For a non–row-returning Command:
cmd.Execute RecordsAffected, Parameters, Options


'Without using command object -see Help topic: Open Method (ADO Recordset)
rsADO.ActiveConnection = con
rsADO.Execute("sql statements for action queries&quot
rsADO.Open "sql statements for returning recordsets", con