Integrating Microsoft Access and QuickBooks

One of the most common uses of Microsoft Access is in data integration projects. Because Quickbooks is so popular, it’s not uncommon for folks to ask me about integrating Quickbooks data with Microsoft Access data. Most of the time, they are seeking to compare and reconcile financial information.

Take, for an example, a nonprofit organization using MS Access as a donor database and Quickbooks for all accounting purposes. Even though the Access application manages demographic information about donors, donor names, giving goals, volunteer activities, etc.. . the donor database also stores monetary donations. These donations should equal all donations recorded in the Quickbooks accounting software.
 
Using Access this way isn’t uncommon at all. Many of my nonprofit clients prefer to record monetary donations in both their donor database and Quickbooks. This gives them a system of checks and balances. In addition, managing monetary donations in two applications helps show accuracy when dealing with auditors or granting organizations. However, this type of donation management also works best when the two databases are integrated. By integrating both databases, it is possible to build reports verifying reported donations balances as accurate.
 
Since Microsoft has been intentional about providing integration tools within Access, it is quite possible to integrate Access and Quickbooks. MS Access already comes outfitted with multiple data connector capabilities. For a Quickbooks integration project, you will need to use the ODBC data connection capabilities. But, first you’ll want to check out CData.com. CData sells 3 different types of Quickbooks ODBC connectors (QuickBooks desktop, QuickBooks Online, and QuickBooks Point of Sale).
 
Once you’ve installed the appropriate QuickBooks ODBC driver, you’re in a position to integrate your production QuickBooks data to MS Access. To link QuickBooks data tables to Microsoft Access go through the following steps:
 

• Click your External Data menu tab
  • Click New Data Source and choose From Other Sources​
    
    • Click ODBC Database. This will initiate an import/link wizard.

• Click Link to the data source by creating a linked table and hit OK button. Choosing Link over Import means that you will be linking to live production data.​

• Once you hit the OK button a Select Data Source dialog box will appear. This is where you choose the QuickBooks ODBC dsn file that would have been set up during the install process. I won’t show a picture here, because ODBC dsn files are stored differently on different systems. The dsn file created during the install process will be stored somewhere on your system, and the Select Data Source dialog box makes it possible for you to navigate to that file. If you need help with this step, feel free to contact me.
• Once you select the QuickBooks data source file and hit OK, a dialog box will appear allowing you to select specific QuickBooks tables to link to. For purposes of learning, just choose the Deposit and DepositLine tables.
  • The DepositLine table stores all details of each deposit. If a single deposit is made up of three checks, you will see one record for each individual check in the DepositLine table.
  • The Deposit table consists of one data row per deposit, with the deposit amount, deposit date, etc..

​After linking both the Deposit and DepositLine tables, you’ll be in a position to use queries and compare QuickBooks data to the data you have in your Access database. Let’s go back to comparing and reconciling donor data between QuickBooks and an Access Donor Database and tackle one of the most common reasons this is necessary. When total donor deposits in MS Access don’t match total donor deposits in QuickBooks, it becomes necessary to reconcile deposit transactions in both accounts.
 
If the MS Access donor database is reporting $200 less in donations than QuickBooks, it is important to figure out where the $200 difference is. Because QuickBooks data is now linked to your Access database, it is possible to compare deposit information and find out where the discrepancy is. Experience has shown me the best way to reconcile monetary transaction data is to query the common data by year and quarter. In this example, if deposit data is summed up by quarter, it becomes pretty apparent which quarter is “off” and then the data can be broken down by month, week, until specific transactions which caused the problem are located.
 
In order to sum data by year and quarter, you’ll want use the MS Access Format() function. Specifically, Format([depositDate],"yyyy q") will output a summary of all deposits by a numerical 4-digit year and quarter. Creating recordsets of both QuickBooks and Access donations by year and quarter gives you a way to actually link the data and compare quarterly totals for discrepancies. Once you’ve isolated which quarters a discrepancy occurs in, you can start drilling down into specific weeks and deposits.
 
This article is merely an overview on how to integrate QuickBooks with Microsoft Access. Keep in mind it is possible to use Access data connectors and integrate with multiple databases at the same time. For instance, it would be possible to link to both QuickBooks and data from other sources such as SalesForce, Dynamics 365, or any number of ODBC database sources. 

More Articles About Microsoft Access​​

• Microsoft Access and Data Connectors
• Using Microsoft Access for Data Evolution Projects
• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Do you have questions about your own data management project? Contact Michelle.