Stored Procedure not returning results

[Winston]

I have a stored procedure that I would like to run from an Access 2003 ADP. I know how to pass the parameters to the stored procedure. The issue I am having trouble with is getting the results to display on the screen.

If I use

Code: Select all

 DoCmd.OpenStoredProcedure 

the stored procedure executes and returns over 2000 records. The stored procedure will display all the results on the screen.

If I pass the parameters to the stored procedure, it does execute but there are no results returned. While debugging I can see that it there is the same number of records.

Is there something that I am missing or forgetting? It has been a while since I had to do anything with Access/SQL/VBA.

Here is the code I am using on the click event of a command button and the attached file is the stored procedure. Currently for testing the parameters are hard coded.

Code: Select all

     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

Thanks,

Winston

[HansV]

Executing an ADO command only opens the result set in memory, so that it can be manipulated in code; it doesn't return a visible result.

You could create a form with DataSheet view as default, and set the Recordset of the form to the result of .Execute in your code above.

[Winston]

Thank you for the quick response. I did not realize or had forgotten that ADO only opens in memory.

Winston