About Access QuickBooks Database
Data in QuickBooks can be accesses using the Open Database Connectivity (ODBC) standard which lets communication and data exchange among different applications. Open Database Connectivity can be used for creating spreadsheets in Excel, tables in Access and reports in Crystal Reports, which reflect QuickBooks data in real time.
The Open Database Connectivity driver can be used for designing reports, with the facility of collating data from several company files, customizing invoices, creating mail merges, among other things. Besides, users can also write their queries using SQL.
Using the Open Database Connectivity driver administrator access can be had to QuickBooks data via third-party programs such as MS Excel, MS Access and Crystal Reports.
Using the Open Database Connectivity Driver with QuickBooks
QuickBooks Enterprise Solutions
This lets users share data between QuickBooks and other programs.
NOTE: Windows Server 2003 and 2008 need the server-only driver. But it isn’t included in the read-only driver of QuickBooks Enterprise Solutions. For additional functionality, users have to upgrade to a read/write driver.
QuickBooks Pro or Premier
Read-only and read-write drivers can be bought for QuickBooks Pro and Premier FLEX quarters. Users don’t have to upgrade to QuickBooks Enterprises Solutions for using the Open Database Connectivity driver.
Installation of Required Open Database Connectivity Driver
QuickBooks Enterprises Solutions
In order to start the installation for the driver, users must go to File-> Utilities-> Setup Open Database Connectivity.
QuickBooks Pro and Premier
Users have to buy and download the driver from FLEX quarters.
For further instructions on the download process, users can contact the Accounting Problem QuickBooks Tech Support Team.
Updating the read-write or server driver
The read-write and server versions of the Open Database Connectivity driver can be bought from FLEX quarters.
Accessibility of data, including payroll
Users can’t access payroll data, multiple “Ship To” addresses, unit of measure and other information due to the limitation set by the QuickBooks Software Development Kit.
QuickBooks Enterprise Solutions
Support for the Open Database Connectivity Driver is only till installation and configuration of the Open Database Connectivity driver for QuickBooks Enterprise Solutions (QBES). Apart from that, troubleshooting assistance for connection problems is provided.
QuickBooks Pro and Premier
Issues related to downloads, purchases and technical support are provided by FLEX quarters for QuickBooks Pro and Premier users.
Users can also buy the read-only, read-write and web server editions and download the QuickBooks Open Database Connectivity Driver for QuickBooks.
Resources related to Open Database Connectivity read-only are available. These are tutorials, troubleshooting, syntax and QuickBooks Open Database Connectivity help.
Setting up Open Database Connectivity in MS Excel
- With QODBC users can merge live QuickBooks data in Excel cells for calculations and graphs. The Dat will have a live link to QuickBooks and data in worksheet can be updated with a click to the latest data in the QuickBooks table that’s linked.
- Prior to using Excel, users must install Microsoft Query Add-on.
- Users must choose menu Data -> Get External Data -> New Database Query. This will open the Data Source screen. Then, users must choose the Data Source that’s open in QuickBooks and then select OK.
- Then, users must select the table to be imported and then choose the columns in the table to be imported by selecting the [>] button. In the next screen that appears, users can filter and sort the data that must be imported. This will then lead to the Finish screen.
- Users can return the data to Excel or choose to edit the data with MS query and apply more conditions to the QuickBooks record set. Then, they must choose Finish.
- The required record set should have only the records with Customer Type that has a value equal to Commercial. Then, users must choose the field header name and click it, which will highlight the column.
- Then, users must choose Criteria/Add Criteria. Next, from the values list (Residential, Commercial), users must choose Commercial/Add.
- Then in the Microsoft Query –[Query From QuickBooks Data] window, the conditions that are applied to the query and the QuickBooks record set will be shown.
- Users must choose File/Return Data to MS Excel and then the Returning External Data to MS Excel screen will prompt for providing the data’s destination.
- Users must select OK for populating the data in the existing Excel sheet.
- The data in the QuickBooks table will be included in the worksheet and the data will be connected to the table from where it had been imported.
- When Data/Refresh Data is selected, the data will be updated in the worksheet from the latest info in the linked QB tables. Any changes to the QB files won’t have any effect on this worksheet, as long as the data isn’t refreshed using the Refresh option.
For further information/help, please call our Accounting Problem QuickBooks Technical Support Team at this toll-free number.
