Querying QuickBooks Data Using SQL via ODBC
For Windows users, RazorSQL provides the ability to connect to databases via ODBC. Since there are ODBC drivers available for QuickBooks, users can use RazorSQL to connect to QuickBooks and query, and in certain circumstances modify, QuickBooks databases using SQL.
In order to use RazorSQL to access QuickBooks data, you will need to have QuickBooks installed on your machine and a QuickBooks company file created.
The next step is to download the QuickBooks ODBC driver. There is a QuickBooks ODBC driver available at http://www.qodbc.com. There is a free 30-day trial version available. After downloading the driver, run the installer to install the driver on your machine.
Next, make sure QuickBooks is running and a company file that you want to query is open.
Before connecting via ODBC, an ODBC data source needs to be configured via the Windows Data Sources control panel. For more general information on how to set up an ODBC data source, see the following: How to Setup an ODBC Data Source. When setting up the data source, you will need to select the "QODBC Driver for QuickBooks" option from the Add data source window.
After the data source is setup, launch RazorSQL, and add a new connection profile using the Connections - Add Connection Profile menu option. Select "Other" for the database type, and "ODBC" for the connection type. In the data source drop down, select the name of the ODBC data source just created via the Windows Data Source control panel.
After hitting connect, a notice may pop up in your QuickBooks session asking if you would like to allow other programs to modify data. Select yes.
At this point, you should be connected to your QuickBooks company file. The QuickBooks information will be listed as tables in the RazorSQL database browser. You can run queries against these tables by writing queries in the SQL editor, or by selecting the table in the database browser and using the "View Contents" option in the right-click menu.
It is possible to modify QuickBooks data using SQL insert, delete, and update statements. However, this requires an intimate knowledge of QuickBooks due to that fact that certain tables have data that is dependent on other tables. Because of this, the data modification needs to be done in a specific order, or else errors will occur.