Parameterised Queries in Access with ADO

[u7djo]

I've built a query in Access and added a parameter to it called
[CurrentPeriod]. I'm trying to load the recordset by using an ADO
command and attaching a parameter to it but I keep getting an error
message when trying to open the recordset (arguments are out of
acceptable range or are in conflict with each other). I've checked the
data types and as much as I can think of - can anyone shed any light on
this? I'm sure I've done it before but can't find the code anywhere and
it's driving me crazy!!!

Thanks

Dave

--- Code ---

Dim o_conn As New ADODB.Connection
Dim rspols As New ADODB.Recordset
Dim m_conn_str As String
Dim i_period As Integer
Dim parmPeriod As New ADODB.Parameter
Dim cmdPBIRecs As New ADODB.Command

m_conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*****"
o_conn.CursorLocation = adUseClient
o_conn.ConnectionString = m_conn_str
o_conn.Open
i_period = CInt(InputBox("Which period are you reporting against?",
"Reports", ""))

cmdGetRecs.CommandText = "qrySalesReport"
cmdGetRecs.CommandType = adCmdStoredProc
cmdGetRecs.ActiveConnection = o_conn
cmdGetRecs.Parameters.Refresh

Set parmPeriod = cmdGetRecs.CreateParameter("CurrentPeriod",
adInteger, adParamInput, , i_period)
cmdGetRecs.Parameters.Append parmPeriod

rspols.Open cmdGetRecs.Execute, o_conn, adOpenForwardOnly,
adLockOptimistic

[Darryl Kerkeslager]

Dim cmdPBIRecs As New ADODB.Command
cmdGetRecs.CommandText = "qrySalesReport"

Which Command object???

Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

Demo: www.adcritic.com/interactive/view.php?id=5927

<u7***@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I've built a query in Access and added a parameter to it called
[CurrentPeriod]. I'm trying to load the recordset by using an ADO
command and attaching a parameter to it but I keep getting an error
message when trying to open the recordset (arguments are out of
acceptable range or are in conflict with each other). I've checked the
data types and as much as I can think of - can anyone shed any light on
this? I'm sure I've done it before but can't find the code anywhere and
it's driving me crazy!!!

Thanks

Dave

--- Code ---

Dim o_conn As New ADODB.Connection
Dim rspols As New ADODB.Recordset
Dim m_conn_str As String
Dim i_period As Integer
Dim parmPeriod As New ADODB.Parameter
Dim cmdPBIRecs As New ADODB.Command

m_conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*****"
o_conn.CursorLocation = adUseClient
o_conn.ConnectionString = m_conn_str
o_conn.Open
i_period = CInt(InputBox("Which period are you reporting against?",
"Reports", ""))

cmdGetRecs.CommandText = "qrySalesReport"
cmdGetRecs.CommandType = adCmdStoredProc
cmdGetRecs.ActiveConnection = o_conn
cmdGetRecs.Parameters.Refresh

Set parmPeriod = cmdGetRecs.CreateParameter("CurrentPeriod",
adInteger, adParamInput, , i_period)
cmdGetRecs.Parameters.Append parmPeriod

rspols.Open cmdGetRecs.Execute, o_conn, adOpenForwardOnly,
adLockOptimistic

[u7djo]

Sorry - was messing round with the names - cmdPBIRecs and cmdGetRecs
are one in the same thing.

[u7djo]

Sorry - was messing round with the names - cmdPBIRecs and cmdGetRecs
are one in the same thing.

[u7djo]

Right, I've finally managed to dig up the piece of code I'd used
previously where I'd managed to pass a parameter in ADO to an Access
Query referencing the query as a stored procedure. The only thing I can
see that is different is that the output of the query wasn't directed
to a recordset (it's a make table). Can anyone explain why I can do one
way but not the other?

Cheers

Dave

Public Function GetPeriodRegistrations() As Integer

Dim o_conn As New ADODB.Connection
Dim cmdLoadRegs As New ADODB.Command
Dim parmPeriod As New ADODB.Parameter
Dim m_conn_str As String

m_conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =
*****;"
o_conn.CursorLocation = adUseClient
o_conn.ConnectionString = m_conn_str
o_conn.Open

On Error Resume Next
o_conn.Execute ("DROP TABLE TMP_RegsPeriodReport;")
On Error GoTo Err_Proc
cmdLoadRegs.CommandText = "qryRegsPeriodReporting"
cmdLoadRegs.CommandType = adCmdStoredProc
cmdLoadRegs.ActiveConnection = o_conn
cmdLoadRegs.Parameters.Refresh
Set parmPeriod = cmdLoadRegs.CreateParameter("ReportingPeriod",
adInteger, adParamInput, , CInt(InputBox("Which period are you
currently reporting against?: ", "Reporting", "")))
GetPeriodRegistrations = parmPeriod
cmdLoadRegs.Parameters.Append parmPeriod
cmdLoadRegs.Execute
Set cmdLoadRegs = Nothing
o_conn.Close

Err_Proc:

If o_conn.State = adStateOpen Then
o_conn.Close
End If
Set o_conn = Nothing
Set cmdLoadRegs = Nothing
Set parmPeriod = Nothing

End Function

[Darryl Kerkeslager]

I believe this line:

rspols.Open cmdGetRecs.Execute, o_conn, [etc]

Should be:

rspols.Open cmdGetRecs, o_conn, [etc]
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

Demo: www.adcritic.com/interactive/view.php?id=5927