asked on

DoCmd.OpenStoredProcedure - passing parameters?

In my ADP I have a couple places where I call DoCmd.OpenView so that my users can see some data in "raw" form (for copying to a spreadsheet, etc.). I've been able to use DoCmd.OpenView successfully with filters to limit the results.

Now I want to do the same thing, but instead of a view, I need a stored procedure because the query is too complicated to simply use filters for the limiting (I'm doing grouping, a subselect, etc). So I see that DoCmd.OpenStoredProcedure exists, but the access documentation doesn't explain how I can pass in my parameters.

Thanks for any help.

rsoble

ASKER

Upping the points because I'm really hoping for a speedy solution!

rsoble

ASKER

I've read a little more and the documentation says "To run the OpenStoredProcedure action in Microsoft Visual Basic, use the OpenView method of the DoCmd object." Okay, so it looks like I should use ither OpenStoredProcedure or OpenView, but the documentation still doesn't tell me how to pass parameters...

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rsoble

ASKER

Thank you, that was very informative. I had done a search before I posted my question, but obviously not with the right search terms - because the answer was so different from what I had expected!

For others searching, here is the solution that worked for me:

Dim sExec As String
sExec = "Alter Procedure spTempSP as Exec spOrderHistory '" & Me.tbxCrop.Value & "'"
DoCmd.RunSQL sExec
DoCmd.RunSQL "GRANT EXECUTE ON spTempSP TO PUBLIC"
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "spTempSP", acViewNormal, acReadOnly

Note the first time I ran it I used create procedure and then afterwards I changed it to alter.
Thanks again. I too am surprised that there isn't a simpler way to do this.