Solved

DoCmd.OpenStoredProcedure  -  passing parameters?

Posted on 2004-09-16
4
1,984 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
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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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