Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DoCmd.OpenStoredProcedure  -  passing parameters?

Posted on 2004-09-16
4
Medium Priority
?
2,097 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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