Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Execute SQL stored procedure in Access Expand / Collapse
Author
Message
Posted Thursday, July 21, 2011 2:22 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:34 PM
Points: 57, Visits: 683
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

  Post Attachments 
SQLScript with Parameters.jpg (87 views, 59.21 KB)
Post #1146273
Posted Friday, July 22, 2011 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:42 AM
Points: 103, Visits: 811
@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?
Post #1146453
Posted Friday, July 22, 2011 2:52 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
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/

Post #1146933
Posted Tuesday, February 14, 2012 10:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
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

Post #1252028
Posted Thursday, September 18, 2014 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
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
Post #1614717
Posted Thursday, September 18, 2014 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 170, Visits: 626
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!
Post #1614846
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse