?
Solved

DoCmd.OpenStoredProcedure  -  passing parameters?

Posted on 2004-09-16
4
Medium Priority
?
2,072 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 1

Author Comment

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

Author Comment

by:rsoble
ID: 12079783
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 2000 total points
ID: 12079900
0
 
LVL 1

Author Comment

by:rsoble
ID: 12080349
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

770 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