Get answers to frequently asked questions about Open Database Connectivity (ODBC) driver and how it works in QuickBooks Desktop.
What is Custom Reporting?
Custom Reporting gives read-only access to your QuickBooks database, then you can create custom reports in ODBC compatible applications like Microsoft Access or Excel.
Note: You’ll be accessing your company file data in QuickBooks using multi-user mode. If you’re not comfortable working with databases, make sure to work with an IT person.
What is ODBC driver?
Open Database Connectivity (ODBC) driver is a standard application that allows admin access to your company file using third-party applications like Microsoft Excel, Microsoft Access, or Crystal Reports.
Through ODBC, you can create spreadsheets in Microsoft Excel, tables in Microsoft Access, and create reports in Crystal Reports that reflect your company file. You can also use this to design your own reports using data from multiple company files, personalize invoices, create mail merges, and more. In addition, you can write your own queries using Structured Query Language (SQL).
How are ODBC users different from QuickBooks users?
ODBC has a separate login from your QuickBooks account. Even if you’re an admin, you’ll need to create new user credentials to connect to QuickBooks for Custom Reporting. ODBC users don’t have the same permissions as QuickBooks users. You can only share login info with users that already have open access to QuickBooks.
Can I use the ODBC driver in QuickBooks?
| QuickBooks Enterprise |
Along with Intuit Developer Network member FLEXquarters, we have a read-only QODBC driver for your company file. That allows you to share data between QuickBooks and hundreds of other applications.
Note: Window Servers (Window Server 2003, 2008, etc) require
the server-only
driver. For
server only or additional features
, you can upgrade to a read/write driver. Fees may apply.
|
| QuickBooks Desktop Pro/Premier |
The read-only and read-write drivers are available in FLEXquarters.
Note: It's not necessary to upgrade to your subscription to use the QODBC driver.
|
How can I install the driver?
To install in QuickBooks Enterprise
- Go to File, then select Utilities.
- Select Setup ODBC, then download and install the ODBC driver.
To install in QuickBooks and Premier
You'll need to buy the driver from FLEXquarters, then you can download and install it.
How can I update the read/write or server driver?
To update your driver, you can purchase it in the FLEXquarters.
How much does the ODBC driver cost?
To learn more about the cost of the ODBC driver, check out the pricing for your version of QuickBooks.
Note: If you’re using the read/write driver, there are certain data limitations for things like payroll data, multiple Ship To addresses, and unit of measure.
What’s the difference between the views and the content shown?
All view names depend on the structure of the info in QuickBooks. For example, a list table will have “lst” in the view name and a transaction will have “txn”. The data will show as header info and line item info.
- V_lst_item: This is a view for the Item List.
- V_txn_invoice_hdr: This is a view for Invoice header information.
- V_txn_invoice_line: This is a view for Invoice line item information.
Can I contact QuickBooks for support?
- QuickBooks Desktop Enterprise: QuickBooks support only assists with installation, connection errors, and configuration of the ODBC driver for QuickBooks Enterprise users. For other technical errors, here's how to get support. Fees may apply.
- QuickBooks Desktop Pro and Premier: FLEXquarters support all purchase, download, and technical help. Fees may apply.
If you have any questions about reporting, check out the Custom Reporting Guide for help. You can also check out tutorials, troubleshooting steps, and support from FLEXquarters.
How do I connect ODBC to Microsoft Excel, Microsoft Access, or Crystal Reports?
A connection is only possible if you’ve created an ODBC user in QuickBooks. After you have created an ODBC user you can connect to Microsoft Excel, Microsoft Access, or Crystal Reports.
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. It’s located in the same folder as your company file. This file has a 'DSN' extension and has the same name as your company file.
- Once you select the file, it will prompt you to authenticate. Use the credentials of the ODBC user that you created to connect to the database.
After you connect, you can start selecting different report views and pull your QuickBooks data into Excel.
Connect to Microsoft Access
- Open Microsoft Access, then open a blank database.
- Select the External Data menu.
- Select the More menu.
- Select ODBC Database.
- Select the Import the source data into a new table in the current database.
- Select OK, then find the file data source that is necessary to make the connection.
- Select the folder icon in the Look in: section. The QuickBooks file datasource is in the same folder as your company file. It has the same name as your company file with a DSN extension. Go to the location of your company file and select the DSN file and enter the credentials of the ODBC user you created.
Once you authenticate, you’ll see a list of database tables. Some of these are system tables that you don’t have access to. Scroll to locate tables that begin with QBReportAdminGroup to see the dataset that’s available for custom reporting. You can select any of the views that begin with QBReportAdminGroup and start creating your reports.
Connect to Crystal Reports
- From Crystal reports, create a new report.
- 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. The QuickBooks file datasource is in the same folder as your company file. It has the same name as your company file with a DSN extension. Go to the location of your company file and select the DSN file and enter the credentials of the ODBC user you created.
Once you authenticate, you’ll see a list of database tables. Some of these are system tables that you don’t have access to. Scroll to locate tables that begin with QBReportAdminGroup to see the dataset that’s available for custom reporting. You can select any of the views that begin with QBReportAdminGroup and start creating your reports.
