If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ADO Parameter Query
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() |
#2
|
|||
|
|||
ADO Parameter Query
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() . |
#3
|
|||
|
|||
ADO Parameter Query
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() . . |
Thread Tools | |
Display Modes | |
|
|