Solved

DoCmd.OpenStoredProcedure  -  passing parameters?

Posted on 2004-09-16
4
1,943 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:rsoble
  • 3
4 Comments
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Upping the points because I'm really hoping for a speedy solution!
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
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...  
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
Comment Utility
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now