February 15, 2009 at 9:22 pm
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.
February 17, 2009 at 1:03 am
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.
February 17, 2009 at 8:00 am
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é
February 17, 2009 at 8:26 am
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.
February 17, 2009 at 12:22 pm
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
February 17, 2009 at 1:00 pm
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?
February 17, 2009 at 1:21 pm
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
February 17, 2009 at 1:52 pm
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. 🙂
February 17, 2009 at 2:14 pm
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
July 21, 2011 at 3:34 am
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
July 21, 2011 at 2:22 pm
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
July 22, 2011 at 2:15 am
"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?
July 22, 2011 at 2:52 pm
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/
February 14, 2012 at 10:07 am
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
September 18, 2014 at 12:41 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply