Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute SQL stored procedure in Access


Execute SQL stored procedure in Access

Author
Message
Naked Ape
Naked Ape
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 703
In an ADP/ADE you can use SQL scripts with parameters through the Data properties of the form/report. Enter the SQL script name (eg. dbo.mySQLScript) into the Record Source property and the parameters into the Input Parameters field (eg. @StartDate datetime=Forms!frmCriteria.txtStartDate, @EndDate datetime=Forms!frmCriteria.txtEndDate)

A minor, annoying feature of Access - if you edit/change the Record Source property Access clears the Input Parameters property, even though the parameters may still be valid.

HTH

Chris
Attachments
SQLScript with Parameters.jpg (96 views, 59.00 KB)
grovelli-262555
grovelli-262555
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 872
@liebesiech
"I hammered a small MDB file together"
When I open your mdb, the Access VBA References window on my pc says I'm missing SQLDMO.dll version 8.5, where can I find it?
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 4869
KKinKC (2/15/2009)
I have an Access 2007 front-end to a SQL database via an ODBC connection. I also have a SQL stored procedure that inputs 4 parameter and builds a table/records that I want to feed back into an Access form.

The Form/Property Sheet/Data/Record Source .... opens a Query Builder window. This window only allows "delete, insert, procedure, select or update" commands. I have been trying unsuccessfully to execute the SQL stored procedure. I always get a "syntax error in PARAMETER clause" regardless of how I code the Procedure statement. The Access help is pretty skimpy on the Procedure clause and I've tried everything.

Any advice or alternatives (w/o programming please) would be appreciated.


I would not use a pass through query. I would execute the SP directly via VBA.

I know that you want to avoid code but it is really not difficult to create a VBA Module that executes the query with the paramaters.

There are a lot of examples on the internet on how to do this.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Fear Naught
Fear Naught
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 65
Thanks for this. i have downloaded your example database and apart from a few errors on built in functions for some reason I have got it working on the Stored Procedures that I need to run.

I particularly like the way the combo box is populated with the names of the procedures.

Kevin
ij1990
ij1990
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
I have used Access 2013 together with ADO recordset to form and subform and pass-through query to report and subreport connected to SQL Server 2000, 2012 and now 2014. Its works fine and not so much code behind the forms and reports that have Me.InputParameters and the others we know from ADP form/report property dialogbox. Linked tables is not good when my tables is like 100,000 rows long and I have always a criteria added in my stored procedures, so only few rows are send from server to client. Im scared of Access made query at linked tables, because how can I be sture, that all data from the tables in the join is not send from server to client, but with ADO recordset and pass-through query I know that will never happen, so I still like making application in Access 2013. I am using ADP+ and ADPX.accde I found at this link
http://www.joakimdalby.dk/HTM/ADPX.htm
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1634
I'm not sure there is a question in your post, but it seems that some clarification is needed. First, the major strength of Access is the ability to bind the data source to forms and reports, eliminating the need to write VBA to populate controls. However you cannot bind a pass-through query to a form or report, so that limits it's appeal. Second, the use of ODBC linked tables isn't a concern unless you are creating queries that join the linked tables. We routinely link to multi-million row tables and get sub-second response time in populating sub-forms that display a few records from the table. Finally, it should be noted that ADP (Access Data Project) support has been removed from Access 2013, although the original post in this thread did not use ADP.

Finally, since this is a 5-year old thread, it might be more useful to create a new thread on how to best deal with the fact that Access 2013 no longer supports the ADP approach, as I suspect quite a few readers would find that more interesting. My personal view is that ADP developments always took far more development time than the linked table MDB approach, and didn't give significantly better performance.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search