Knowledgebase: Sp_commands & Reports
[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 helpful 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, click on the Queries Object, select New -> Design View query as shown below, and 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:

     ODBC;DSN=QuickBooks Data;SERVER=QODBC

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:

 


Also, Refer :
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2010 (Import Tables) 32-bit
Using QuickBooks Data with Access 2010(Linked Tables) 32-bit
Using QuickBooks Data with Access 2013(Import Tables) 32-bit
Using QuickBooks Data with Access 2013 (Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access

 

(182 vote(s))
Helpful
Not helpful

Comments (4)
Karl Voss
16 April 2015 05:46 PM
I am trying to use ACCESS 2013 (32bit) with a passthrough query. Where do I set the connection string (right now I have to click the ODBC source over and over)?
Jack
17 April 2015 09:19 AM
Hi Karl,

You can save pass through connection string by clicking "Property Sheet" & provide connection string in "ODBC Connect str" box.
Tom Spencer
24 May 2017 04:24 PM
I need to extract a custom report from QB Enterprise Solutions that shows the P&L for a particular month, and for a period of years. For example, I would like to see a P&L for the month of April only, for the years 2014, 2015, 2016, and 2017. Can it be done with ODBC and if so, can you point me to instructions for doing it with MS Access?
Jack
25 May 2017 06:54 AM
Hi Tom,

You can use DateFrom & DateTo parameters for running the report for the specific period.

For Example:
Running report for April 2017:
sp_reportProfitAndLossStandard show Text, Label parameters DateFrom= {d'2017-04-01'}, dateto= {d'2017-04-30'}

For running report 2016:
sp_reportProfitAndLossStandard show Text, Label parameters DateFrom= {d'2016-01-01'}, dateto= {d'2016-12-31'}

Same way you can run the report for other years.

If you are still facing the issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages

Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).