Running a stored procedure from adp

Topic

New Reply

I have a select query/stored procedure that I would like to return the results from a command button on a form. This is an Access Data Project (.adp) file. The stored procedure is just a select statement with dates, a grouping type (daily, weekly, monthly), and detail or summary being passed as parameters.

If I use

Code:

DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

and enter the paramaters by hand the stored procedure returns the results.

If I pass the parameters from, a form, to the stored procedure, it will run but not return any results. I am using the following code to pass the parameters.

Code:

    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command
    
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = “spPRDT”
        .CommandType = adCmdStoredProc
        
        .Parameters.Append .CreateParameter(“@Sdate”, adVarChar, adParamInput, 20, “08/01/2014”)
        .Parameters.Append .CreateParameter(“@EDate”, adVarChar, adParamInput, 20, “08/31/2014”)
        .Parameters.Append .CreateParameter(“@qryType”, adVarChar, adParamInput, 1, “1”)
        .Parameters.Append .CreateParameter(“@qryGroup”, adVarChar, adParamInput, 1, “d”)
        .Execute
    End With

I am not sure if this is the correct method of passing the parameters and executing the stored procedure on just a select statement.

Thanks in advance for any input.

Reply | Quote