SQL Clone
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
KKinKC
KKinKC
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 145
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.
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 2235
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.
liebesiech
liebesiech
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 862
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é
Attachments
ExecuteSP.zip (520 views, 100.00 KB)
KKinKC
KKinKC
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 145
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.
Naked Ape
Naked Ape
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 705
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
KKinKC
KKinKC
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 145
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?
Naked Ape
Naked Ape
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 705
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
KKinKC
KKinKC
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 145
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. Smile
Naked Ape
Naked Ape
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 705
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

Wink

Chris
imfarhan
imfarhan
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
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