[QODBC-Desktop] How to create Pass-Through Report using Microsoft Access 2003
Posted by Juliet (QODBC Support) on 22 February 2010 03:01 AM
Instructions for Pass-Through Query in Access
Note: "Pass-through" reports or queries are useful as it allows specific SQL statements to be sent directly to QODBC (including connection settings), bypassing the MS Access syntax checker.
How to create Pass-Through Report in Access 2003
Create a new Query in Access 2003
Open Microsoft Access 2003, create a new mdb and click on the Queries Object and select New -> Design View query as shown below, then press OK.
Close the Show Table pane:
Setup Pass-Through Query
Right-click within the Design view pane and select the SQL Specific -> Pass-Through query as shown:
Paste the following example of Payroll Summary Report or your own sp_report into the pane:
sp_report PayrollSummary show Label, AmountHours, AmountRate, Amount parameters DateMacro = 'ThisQuarterToDate' where RowType='DataRow'
and then right click on the (blue)Title bar and select Properties to set up the connection parameters:
Change the ODBC Connect Str to call QODBC like this:
and open the pass-through query to see the end result:
Setup of Query Properties
The default timeout in MS Access is 60 seconds, for large queries you will need to increase the query property to 600 seconds: