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 Sunday, February 15, 2009 9:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 01, 2014 7:10 PM
Points: 17, Visits: 140
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 parameters 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.
Post #657473
Posted Tuesday, February 17, 2009 1:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:33 AM
Points: 357, Visits: 1,928
Try running the SP via a pass through query. We do that in Access 97 on a regular basis. You can also use pass through queries to execute queries that use real SQL rather than the crap Access generates.
Post #658272
Posted Tuesday, February 17, 2009 8:00 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:08 AM
Points: 130, Visits: 795
Hi
Its a while since I had to do something with MS Access and SQL Server but I can remember some stuff I have done. Based on a tool for running different SP used in a migration I hammered a small MDB file together.

The file has the following implemented:
-Execute a SP and returning the result set into an Excel sheet or text file.
-Execute a SP using parameters and returning a message (it just calculates Number1 * Number2)

Before you can use the database you have to
-enter your server name in the mdlPublicDeclaration
-create the following two SP in the AdventureWorks database. The code to create the SP can be found in the database as well.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Calculate]
@Number1 [int],
@Number2 [int],
@returnMessage VARCHAR(50) OUTPUT --Parameter holding the return message
AS
BEGIN
SET @returnMessage = @Number1 * @Number2
SET @returnMessage = 'The result of this calculation is : ' + @returnMessage
End

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetEmployeeInfo]
AS
BEGIN
SET NOCOUNT ON;
SELECT Person.Contact.FirstName, Person.Contact.MiddleName, Person.Contact.LastName, Person.Contact.EmailAddress,
HumanResources.Employee.Title
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID AND
Person.Contact.ContactID = HumanResources.Employee.ContactID
End

Once the SP is created you can open form frmExecSP_CreateReport or frmExecSP_WithParameter and hit the run button. If you open frmExecSP_CreateReport make sure you select the SP usp_GetEmployeeInfo.

Hope this helps.
René


  Post Attachments 
ExecuteSP.zip (453 views, 100.85 KB)
Post #658538
Posted Tuesday, February 17, 2009 8:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 01, 2014 7:10 PM
Points: 17, Visits: 140
Thanks all for the suggestions. I've learned how to do a pass-thru query then hit the next stumbling block : how to use information from Access within the pass-thru query. When I tried a where clause with a '= Forms!frmWaterQuality!StartTime', it didn't like it at all. The same is true for executing a stored procedure using fields from an Access form as parameters.

Is there a way to do this? The referencing notation of Access doesn't come close to what SQL will accept. I don't know how or if it is even possible to define a variable in Access that I can use in a SQL pass-thru query.
Post #658564
Posted Tuesday, February 17, 2009 12:22 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 02, 2014 6:18 PM
Points: 58, Visits: 667
In an MDB/MDE you can try setting the filter parameters (in the Report properties) - for example;

Record Source: select * from myPassThroughQuery;
Filter: myColumnName=Forms!frmWaterQuality!StartTime
Filter On: Yes

Note - anytime you enter/amend the Record Source property, the Filter property is cleared and must be reset.

Depending on the size of the result set, this can be a bit slow. The SQL pass-through query is executed on the SQL server, with the results returned to Access where the filter is applied - so you can be returning a large result set to the client that Access must then filter.


If you are using an ADP/ADE there is an equivalent property called 'Input Parameters', which has a similar syntax.

HTH


Chris
Post #658738
Posted Tuesday, February 17, 2009 1:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 01, 2014 7:10 PM
Points: 17, Visits: 140
What I am trying to do:
1. user chooses variables from a form, i.e. a user, start time, end time, record type
2. run a pass-thru query that executes a SQL stored procedure that has the user's choices as parameters [the stored procedure does commands that Access is not capable of]
3. the stored procedure returns records to the form

My problem is that I haven't found a way to include the choices from the Access form as parameters in the pass-thru query.

I am a total novice at Access so it will take some time to try Chris' suggestion. Are there any other suggestions out there?
Post #658772
Posted Tuesday, February 17, 2009 1:21 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 02, 2014 6:18 PM
Points: 58, Visits: 667
With a standard Access query you can embed references to a field on an Access form (ie: Forms!FormName!ControlName), but Pass Through queries are sent to the SQL server as written - Access does not attempt to parse the SQL statement first, only allowing you to filter the results.

The only options I have seen/used to do this are the Filter parameters described previously, or through VBA code to manipulate the SQL Pass Through query before it is sent to the SQL server.

The VBA to do this is quite simple (just a few lines) so if anyone wants a sample I can post one.


Chris
Post #658801
Posted Tuesday, February 17, 2009 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 01, 2014 7:10 PM
Points: 17, Visits: 140
Thanks Chris! I'll take everything I can get. As soon as I finish this I can go back to teh normal SQL world and Access will be a dim memory. :)
Post #658848
Posted Tuesday, February 17, 2009 2:14 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 02, 2014 6:18 PM
Points: 58, Visits: 667
Through VBA you can add a few lines of code to the Report_Open event - for example;

Private Sub Report_Open(Cancel As Integer)
Dim DB As Database
Dim Q As QueryDef

Set DB = CurrentDb()
Set Q = DB.QueryDefs("MyAccessQuery")

Q.SQL = "exec dbo.mySQLSP " & Forms!frmReportOptions.txtCriteriaField
End Sub


This assumes (1) the Pass Through query has been saved in Access, and as MyAccessQuery, and (2) the query will execute a SQL stored-procedure with a single numeric parameter - a string parameter would need to be enclosed in single-quotes.

Note, the above code permanently changes the Access pass-through query, even though we don't actually 'save' it. For completeness - you can reset the pass-through query again when the report closes (in the Report_Close event), but it's not required.

Private Sub Report_Close()
Dim DB As Database
Dim Q As QueryDef

Set DB = CurrentDb()
Set Q = DB.QueryDefs("MyAccessQuery")

Q.SQL = "exec dbo.mySQLSP 0"
End Sub

;)


Chris
Post #658882
Posted Thursday, July 21, 2011 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 3:27 PM
Points: 1, Visits: 8
Hi i've got similar problem

The procedure called from SQL database and would like to run through Ms Access
In Ms Access I already running Queries using following function

Private Function ExecuteQuery(sQueryName As String) As ReturnStatus
Dim QryDB As DAO.Database

On Error GoTo Err_Handler


Set QryDB = DBEngine.Workspaces(0).OpenDatabase(sCOREDB, False, False)

DoCmd.SetWarnings False
QryDB.Execute sQueryName
DoCmd.SetWarnings True
DoEvents


The above function execute all the SQLs from the table, but now I would like to run/execute the
procedure I've copied the procedure in the same table where I have all queries the queries table also keep the field QRY_Type i.e. "QRY","QBT" and so on , QRY=Simple SQL query QBT= MAketable,update, append queries
Now I'm thinking I think I can control by using IF like sQueryName = "ProcedureQuery" THEN ...
OR IF the Query type "PRO" THEN call specific function... to run the procedure but I don't know how?

I hope it does make sense to you.

Many thanks for you help
Farhan
Post #1145713
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse