Parameterised Queries in Access with ADO

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

re: Parameterised Queries in Access with ADO

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



<u7djo@hotmail.com> wrote in message
news:1109257446.504843.86970@z14g2000cwz.googlegro ups.com...[color=blue]
> 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
>[/color]

re: Parameterised Queries in Access with ADO

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

re: Parameterised Queries in Access with ADO

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

re: Parameterised Queries in Access with ADO

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

re: Parameterised Queries in Access with ADO

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