How to Migrate Transactions from Microsoft Access to QuickBooks Part 1
This tutorial provides a simple function to migrate data from Microsoft® Access to QuickBooks® using QODBC, an Open Database Connection for QuickBooks®.
You do not need to be a programming expert or even know SQL to use this tutorial because the SQL is written for you. Just copy and paste, follow instructions, and migrate away.
The video below shows the migration process as checks are inserted programmatically from Microsoft® Access to QuickBooks®. The speed remains consistent as data is pulled from the database and inserted into the check register. The code can also be used with:
credit card charges
credit card credits
checks (other than payroll checks which require sp_reports)
That is how fast it really works.
You do not need QuickBooks® SDK for this function. All you need is Microsoft® Access, QuickBooks® and QODBC.
This function uses SQL (Structured Query Language) to read from and write to QuickBooks®. An example would be:
select * from account
The above SQL would be used in a Pass-Through query to extract all the accounts and their fields and values from QuickBooks®. If you did not understand that sentence do not worry! The function at the end of the tutorial is ready to use. It creates the Pass-Through query for you.
To migrate data from another bookkeeping application, import the transactions into Microsoft® Access tables for exporting to QuickBooks®.
The pre-written code uses DAO, so you need to select the DAO reference in your database. If you do not know how to select references, see the post: Chapter 01: Part 2: How to Create a New Module and Select References on my other site: The Generic Database. (http://www.thegenericdatabase.com/2011/09/03-references.html).
Step 1: Copy Your Tables
After importing transactions into Microsoft® Access, you need to match field names to QuickBooks® field names. Your computer is not a magician nor is it a mind reader. You must tell it what fields to insert data to. Copy the imported tables and change the copies so you still have the original data.
For this particular code, you will need separate tables for each transaction type such as:
Therefore, when exporting transactions from your old application into your database, export them by transaction type so each table contains only one transaction type. In other words, export only checks and then export only bills, etc.
Step 2: Match Field Names
In your copied tables, rename fields to match QuickBooks® field names. The code iterates through the recordset using the recordset field names for SQL. Do not worry if you do not understand this, just know you must match your table field names to QuickBooks® fields names.
For example: the transaction date field of your table must be named: TxnDate. The check number or invoice number must be named: RefNumber. Do not expect your computer to know that transaction date is supposed to be inserted into the TxnDate field.
Match your table fields by looking them up with VBDemo or build yourself a quick and simple form that provides QuickBooks® field names from the tutorial here on VBQuick: QuickBooks Tables in Access w/ QODBC Form at: www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html.
To change field names, open the copied tables in design view.
Step 2: Single Line Items
This code migrates single line items - in other words, checks with only one expense line or one item line, invoices with only one invoice line.
ASAP I will post code to migrate multiple line transactions. This is a quick example and quick start to let you see how simple migration can be.
What are Line Items?
When you look at a check in QuickBooks®, the colored area (top of the check) contains data stored in the Check table.
The bottom of the check (check stub) contains data stored in the CheckExpenseLine table.
When you select the Expenses tab, the data is stored in the CheckExpenseLine table.
When you select the Items tab, the data is stored in the CheckItemLine table.
To insert checks into QuickBooks®, insert into the CheckExpenseLine or CheckItemLine table. The Check table automatically updates.
This applies to all the transaction tables. Some examples:
This insert automatically updates this table
Step 3: Use Only Insertable Fields in Your Table
Your table fields must be insertable. Look them up with VBDemo or create the form referenced above to avoid repeatedly opening VBDemo to check field names and insertable values.
For example, let us say your check table has a field named Amount for the amount of the check. Amount is a field in both the Check and the CheckExpenseLine tables. However, Amount is not insertable in either table.