QuickBooksHelpIntuit

Set up Open Database Connectivity (ODBC) Driver for QuickBooks Desktop

by Intuit• Updated 3 weeks ago

The Open Database Connectivity (ODBC) driver gives read-only access to your QuickBooks database, allowing you to create custom reports in compatible third-party applications like Microsoft Excel, Microsoft Access, or Crystal Reports. This driver enables you to design reports using data from multiple company files, personalize invoices, create mail merges, and write your own queries using Structured Query Language (SQL).

About ODBC and Custom Reporting

Custom Reporting provides read-only access to your company file data. You will access this data in QuickBooks using multi-user mode. If you are not comfortable working with databases, you should work with an IT professional.

ODBC User Credentials

ODBC requires a separate login from your QuickBooks account. You must create new user credentials to connect to QuickBooks for Custom Reporting, even if you are an admin. These users do not have the same permissions as QuickBooks users. You can only share login info with users that already have open access to QuickBooks.

Driver Compatibility and Cost

The following table outlines driver availability and features for your version of QuickBooks:

QuickBooks Version	Driver Details
QuickBooks Enterprise	Includes a read-only QODBC driver developed with FLEXquarters to share data with other applications. Window Servers (2003, 2008, etc.) require the server-only driver. You can upgrade to a read/write driver for additional features (fees may apply).
QuickBooks Desktop Pro/Premier	Read-only and read-write drivers are available for purchase from FLEXquarters. It is not necessary to upgrade your subscription to use the QODBC driver.

To learn more about the cost of the ODBC driver, check out the pricing for your version of QuickBooks. To update your driver, or buy the driver for Pro/Premier, you can purchase it in the FLEXquarters

Note: If you use the read/write driver, there are data limitations for payroll data, multiple Ship To addresses, and unit of measure.

Install the ODBC Driver

For QuickBooks Enterprise:

Go to File and select Utilities.
Select Setup ODBC.
Download and install the ODBC driver.

For QuickBooks Desktop Pro and Premier:

You must buy the driver from FLEXquarters before you can download and install it.

Connect ODBC to Third-Party Applications

A connection is only possible if you have created an ODBC user in QuickBooks. The QuickBooks file datasource is located in the same folder as your company file, has the same name as your company file, and ends with a 'DSN' extension.

Connect to Microsoft Excel:

From Excel, select the Data menu.
Select From Other Sources.
Select From Microsoft Query.
Select Browse to locate the file datasource.
Select the DSN file to prompt authentication.
Enter the credentials of the ODBC user you created to connect to the database.
Select different report views to pull your QuickBooks data into Excel.

Connect to Microsoft Access

Open a blank database in Microsoft Access.
Select the External Data menu.
Select the More menu.
Select ODBC Database.
Select Import the source data into a new table in the current database.
Select OK, then locate the file data source necessary to make the connection.
Select the folder icon in the Look in: section to find your company file folder.
Select the DSN file and enter the credentials of the ODBC user you created.
Once authenticated, scroll to locate tables that begin with QBReportAdminGroup to see the available dataset.
Select any view starting with QBReportAdminGroup to create reports.

Connect to Crystal Reports

Create a new report in Crystal Reports.
Open the Database Expert.
Select Create New Connection.
Select ODBC (RDO).
Select Find File DSN.
Select the button next to the File DSN field to locate the file datasource.
Go to the location of your company file, select the DSN file, and enter the credentials of the ODBC user you created.
Scroll to locate tables that begin with QBReportAdminGroup.
Select any view starting with QBReportAdminGroup to create reports.

Report View Structure

View names depend on the structure of the information in QuickBooks. Data shows as header information and line item information.

List tables: Include "lst" in the view name (for example, V_lst_item for the Item List).
Transactions: Include "txn" in the view name.
- V_txn_invoice_hdr: View for Invoice header information
- V_txn_invoice_line: View for Invoice line item information.

Support for ODBC Driver

QuickBooks Desktop Enterprise

QuickBooks support assists with installation, connection errors, and configuration of the ODBC driver for QuickBooks Enterprise users only. Fees may apply for other technical errors.

QuickBooks Desktop Pro and Premier

FLEXquarters supports all purchase, download, and technical help. Fees may apply.
Check out the Custom Reporting Guide for questions about reporting, or access tutorials, troubleshooting steps, and support from FLEXquarters.

QuickBooks Desktop Mac PlusQuickBooks Desktop Premier PlusQuickBooks Desktop Pro Plus