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
invoices
deposits
bills
bill 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:

      check
      bill
      invoice
      deposit
      creditcardcharge
      creditcardcredit
      billcredit

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 run a QuickBooks® Custom Transaction Detail report for checks and view the Debit and Credit columns, the bottom entries (from above) appear in the Debit column.





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
      CheckExpenseLine                                         Check
      CheckItemLine                                               Check
      BillExpenseLine                                               Bill
      CreditCardChargeExpenseLine                       CreditCardCharge
      InvoiceLine                                                     Invoice
      DepositLine                                                    Deposit




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.



Insert amounts into checks using the table CheckExpenseLine and the field ExpenseLineAmount. QuickBooks® adds the line amounts and inserts the sum into the Amount field. You cannot change this. You must rename your field named Amount to ExpenseLineAmount so the code uses the proper field name.





If you try to insert a non-insertable field an error stops the code and the data is not inserted.

Open your copied tables in Design view and:
     
      1. delete non-insertable fields
      2. rename fields to match QuickBooks® fields



Continue...

Do you like this post? Please link back to this article by copying one of the codes below.

URL:

HTML link code:

BB (forum) link code:

No comments:

Post a Comment