Module to execute Query w/Parameters?

[cj92713696]

I have created a query that works perfect. It has 4 parameters - StoreCode, ItemCode, StartDate, EndDate.

I want to create a simple function in a module that calls the Access query, passing the four parameters and grabbing the results via an ADODB.Recordset.

Can anyone help me with this?

Thanks,
CJ

 

[cmmrfrds]

There are many ways to write the code. Here is an example of a function that I setup to do something similar. This example uses output parameters to bring values back. I am picking up the values by ordinal position, but you can do it by name if you give your parameter a unique name which I did not.

If your SP brings back a resultset then just equate the execute to the recordset that you set up. i.e. rst = cmd.Execute

Function GetVariousCounts()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim param4 As Parameter, param5 As Parameter

pubCaseCount = 0
pubInvoiceCount = 0

' Connect
Set cnn = CurrentProject.Connection

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.sp_variousCounts"
cmd.CommandType = adCmdStoredProc

' Set up input parameters.
Set param1 = cmd.CreateParameter("Input", adDouble, adParamInput)
cmd.Parameters.Append param1
param1.Value = pubEmployeeID
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = pubBegDate
Set param3 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param3
param3.Value = pubEndDate

' Set up output parameters.
Set param4 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param5

' Execute command to run stored procedure
cmd.Execute
' ordinal position relative to 0
pubCaseCount = cmd(3)
pubInvoiceCount = cmd(4)

''Debug.Print " pubCaseCount = "; pubCaseCount

End Function

 

[cj92713696]

I can't get the parameters fields to work properly.

My parameters are ItemCode, StoreCode, StartDate, and EndDate.

ItemCode is a string of 10 characters, StoreCode is a string of 3 characters, and StartDate and EndDate are date values.

When I declare my parameters, I get the error "Parameter object is improperly defined.

Example -

Set param1 = cmd.CreateParameter("StoreCode", adVarChar, adParamInput)
cmd.Parameters.Append param1
param1.Value = storeCode

Thanks,
CJ

 

[cmmrfrds]

The varchar (string data types) requires a length, which is the 4th parm.

Set param1 = cmd.CreateParameter("StoreCode", adVarChar, adParamOutput, 20)

 

[oharab]

Have a look at faq701-1964 for my way of doing it.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[cj92713696]

Thanks, everything works great!

I really appreciate your help.