pass through query

Question

have created a pass through query in access as

execute spfiscalsocialstatsnotsatorholidays forms!f_main!txtbegindate, forms!f_main!txtenddate

an error is created when i try to put this query (qrySocialSaturdays) into a different query that a report will be based on. the error is

odbc--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '!'.(#102)

I have text boxes on a form in MsAccess whose values need to be passed to the above stored procedure.

I have also tried opening the pass through with a button on a form and the same error occurs.  apparently sql does not like '!' so how should i reference data in a textbox on a form which is to be used for the parameter on the stored procedure on sql server 2008

Answer 1

 have created a pass through query in access as

execute spfiscalsocialstatsnotsatorholidays forms!f_main!txtbegindate, forms!f_main!txtenddate

an error is created when i try to put this query (qrySocialSaturdays) into a different query that a report will be based on. the error is

odbc--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '!'.(#102)

I have text boxes on a form in MsAccess whose values need to be passed to the above stored procedure.

 

I have also tried opening the pass through with a button on a form and the same error occurs.  apparently sql does not like '!' so how should i reference data in a textbox on a form which is to be used for the parameter on the stored procedure on sql server 2008

If this is a pass-through query, then the SQL of the query will be passed directly to SQL Server without any attempt to resolve the parameters.  SQL Server doesn't know anything about the forms and controls that Access is managing, so the query is bound to fail.

The only resolution I can think of is to modify the SQL of the query dynamically, before it is used, to replace the placeholders with the actual values of the form controls, or for that matter just to set its SQL on the fly.  For example, if your query is named "qryPassThrough", you could write this:

    CurrentDb.QueryDefs("qryPassThrough").SQL =

        "execute spfiscalsocialstatsnotsatorholidays " & _

            Format(forms!f_main!txtbegindate, "'yyyy-mm-dd'" & ", " & _

            Format(forms!f_main!txtenddate, "'yyyy-mm-dd'"

Then you could execute the query, or other queries based on this query, because you yourself have placed the parameters into the query SQL.