Knowledgebase: QODBC
[QODBC-ALL] How to export QuickBooks Invoices into MS Access report format
Posted by Jack - QODBC Support on 15 June 2017 10:11 AM

How to export QuickBooks Invoices into MS Access report format

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Microsoft Access.

The attached invoice is similar to what we need to export to Access. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Access report format using QODBC.

The first step is to create a new MS Access database & link the InvoiceLine table to MS Access.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office.

 

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QB Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database.

After the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button on the External Data menu:

Under the Get External Data window, you will get two options. We will use "Link to the data source by creating a linked table":

After selecting the access method and clicking OK, you will get a Select Data Source window.

Select the QuickBooks Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the Example.)

For 64-bit MS Office, change QRemote DSN to QuickBooks Data 64-bit QRemote is the default DSN created when installing QODBC.

Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

The linked table was added to MS Access.

Now, We will design a new report from Create->Report Wizard

The Report Wizard shows showing Linked table & available fields.

Select the fields you want to show in the report & click "Next."

Report Wizard showing selected fields. Click "Next" for more configuration.

You can sort records in ascending or descending order up to four fields & click "Next."

Select report Layout & click "Next."

Insert the report name & select "Modify the report's design," & click "Finish" to design a report.

The report design window is opened & Drag the table field into the report as per your requirement & design the information.

Select table field assigns a group to table fields by clicking the "Add a Group" button.

The select field for Group. In this example, I have selected "TxnID" all group fields are grouped for a particular TxnID.

The Group is created. Click "More."

Select "With a header section" & "With a footer section" to enable Group Header & Footer.

If you want to print one invoice per page (i.e., Only one invoice on a single page), click on the group footer for TxnID & set the Force New Page property box drop-down arrow, and select Before Section.

The report is designed. You can add labels & assign a group to table fields. You can view the information by clicking on "View."

The actual report will look as below in View mode.

You can select "Print Preview" mode to see all Invoice reports.

The Invoices in MS Access report format are shown below.

Please click here to download Sample Report.

 

(0 vote(s))
Helpful
Not helpful

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