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: Tuesday, July 15, 2014 7:14 PM
Points: 58, Visits: 676
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 (84 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: Friday, July 11, 2014 1:51 PM
Points: 100, Visits: 788
@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: Yesterday @ 6:10 AM
Points: 4,179, Visits: 4,260
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse