ADO Parameter Query

[Tony_VBACoder]

I have a bunch of Parameter Queries that I have been
calling with VBA. But now I am converting my code to use
ADO recordsets. How would I change my old code to ADO
(see below)?

Private Sub GetParameterQuery()
Dim dbs As Database, rst As Recordset, qdf As QueryDef
' Set database variable to current database.
Set dbs = CurrentDb
' Set the QueryDef object
Set qdf = dbs.QueryDefs("qryMyParameterQuery")
' Set the parameters of the query
qdf.Parameters("[Start Year]") = 2000
qdf.Parameters("[End Year]") = 2004
' Open Recordset object
Set rst = qdf.OpenRecordset()

 

[Gerald Stanley]

Try something along the lines of

Private Sub GetParameterQuery()
Dim comm As ADODB.Command
Dim pm As ADODB.Parameter
Dim rst As ADODB.recordset

Set comm = New ADODB.Command
Set comm.ActiveConnection = CurrentProject.Connection
comm.CommandText = "qryMyParameterQuery"
comm.CommandType = adCmdStoredProc
Set pm = comm.CreateParameter("Start Year", adNumeric , ,
, 2000)
comm.Parameters.Append pm
Set pm = comm.CreateParameter("End Year", adNumeric , , , 2004)
comm.Parameters.Append pm

Set rst = comm.Execute

Hope This Helps
Gerald Stanley MCSD

 

[Tony_VBACoder]

Gerald,

Worked perfect. Thank you.

-----Original Message-----
Try something along the lines of

Private Sub GetParameterQuery()
Dim comm As ADODB.Command
Dim pm As ADODB.Parameter
Dim rst As ADODB.recordset

Set comm = New ADODB.Command
Set comm.ActiveConnection = CurrentProject.Connection
comm.CommandText = "qryMyParameterQuery"
comm.CommandType = adCmdStoredProc
Set pm = comm.CreateParameter("Start Year", adNumeric , ,
, 2000)
comm.Parameters.Append pm
Set pm = comm.CreateParameter("End Year", adNumeric , , , 2004)
comm.Parameters.Append pm

Set rst = comm.Execute

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a bunch of Parameter Queries that I have been
calling with VBA. But now I am converting my code to use
ADO recordsets. How would I change my old code to ADO
(see below)?

Private Sub GetParameterQuery()
Dim dbs As Database, rst As Recordset, qdf As QueryDef
' Set database variable to current database.
Set dbs = CurrentDb
' Set the QueryDef object
Set qdf = dbs.QueryDefs("qryMyParameterQuery")
' Set the parameters of the query
qdf.Parameters("[Start Year]") = 2000
qdf.Parameters("[End Year]") = 2004
' Open Recordset object
Set rst = qdf.OpenRecordset()



.

.