By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Parameterised Queries in Access with ADO

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
Sorry - was messing round with the names - cmdPBIRecs and cmdGetRecs
are one in the same thing.

Nov 13 '05 #3

P: n/a
Sorry - was messing round with the names - cmdPBIRecs and cmdGetRecs
are one in the same thing.

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.