[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
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.
You can export QuickBooks Invoices into MS Access report format using QODBC.
The first step is to create new MS Access database & link InvoiceLine table into 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 ODBC database button on External Data menu:
Under Get External Data window, you will get two options. We will use "Link to the data source by creating a linked table":
After you select access method and click OK, then 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 Select Tables window. Please, select tables you want to extract data from. In this Example, I am selecting InvoiceLine table.
The linked table added into MS Access.
Now, We will design new report from Create->Report Wizard
The Report Wizard is showing Linked table & available fields.
Select fields which 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 report name & select "Modify the report's design" & click "Finish" for designing report.
The report design window is opened & Drag the table field into the report as per your requirement & design the report.
Select table field assigns a group to table fields by clicking "Add a Group" button.
Select field for Group. In this example, I have selected "TxnID" all group fields are grouped for particular TxnID.
The group is created, Click "More".
Select "With a header section" & "With a footer section" for enabling Group Header & Footer.
If you want to print one Invoice per page (i.e. Only one Invoice in the single page), then click on the group footer button for TxnID & set 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 report 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 is shown as below.
Please click here for downloading Sample Report.