How to Migrate Transactions from Microsoft Access to QuickBooks Part 3

This is a 3 part tutorial that begins here.

Test The Function

If you run into problems, test the function like this.

Create a copy of your QuickBooks® file to use as a test file.

Create a new table in your database with these fields exactly as they are written. Do not mispell, add characters, add fields, etc. These are the only fields you need for a test.

AccountRefFullName must be Data Type Text.

Memo must be Data Type Memo.

ExpenseLineAccountRefFullName Data Type must be Text.

Name Table Fields


Change to Datasheet View and enter values.


Do not use the values below!!!

For AccountRefFullName, use the name of a checking account in your test QuickBooks® file. To keep things simple, do not use a subaccount of another bank account. Use a parent bank account for this test. Type the exact name exactly as it appears in your QuickBooks® file.

For ExpenseLineAccountRefFullName, type the name of one of your expense accounts. Again, do not use a subaccount. Keep this test simple and use a parent expense account and type the name exactly as it appears in your QuickBooks® file.






Close the table. Name the table: test.



Open the Visual Basic Editor and type fncWriteData in the Immediate Window.






Type test as the name of the Microsoft® Access table you wish to transfer.





Type checkexpenseline as the name of the QuickBooks® table you wish to write to.






Give QODBC enough time to connect to QuickBooks® and write the check. Initial connections take a few extra seconds but thereafter writing is quick.

Open the bank account whose name you entered in the test table and look for the check. It will not have a payee because we did not include one so it should be easy to find.





Open the check. Notice the memo you entered in the test table.






If you followed these instructions to the letter but are still having problems migrating data from your Microsoft® access table, the problem must lie in your database table.

To recap problem areas:

  1. Make sure AccountRefFullName,VendorRefFullName and CustomerRefFullName values exactly match those in your QuickBooks® file. For example, ABCStore and ABC Store with a space are not the same.
  2. Do not use ExpenseLineAccountRefListID, AccountRefListID,VendorRefListID and CustomerRefListID. Stay away from ListIDs unless you are absolutely positive you are using the right ones for the corresponding names.
  3. Do not use BillableStatus. Alternatively, write a function that excludes BillableStatus from SQL when Expense Line Account types do not allow it. For example, you cannot select a Billable Status for a payment against a credit card. It is not available. Trying to insert it will cause an error.
  4. Make sure your table field sizes match QuickBooks® field sizes. For example, RefNumber only provides 11 characters. QODBC will not truncate RefNumber but instead cause an error for too many characters.
  5. Make sure you are including all required fields. For example, to find required fields for checks, type sp_columns checkexpenseline into VBDemo and scroll over to the Required_On_Insert column.
  6. Make sure the values for required fields are not Null. For example, you may have included the required field AccountRefFullName and that is good. However, if any of your records have a Null value in that field, an error will occur.
  7. Make sure you are only using Insertable fields. Use VBDemo and sp_columns checkexpenseline (or the table you are writing to) and scroll to the Insertable column. A typical mistake is using the field Amount when you should instead use the field ExpenseLineAmount.
The function prints each SQL statement in the Immediate Window of the Visual Basic Editor. The last SQL statement will be the one that caused the error. Copy it and paste it inot VBDemo for more information on the error.

Let me know how this works for you.

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:

1 comment:

  1. Hi.

    Your site is great and I have learned so much. I used you example above and learned a great deal about DOA and it counter parts ADO and others caught in the the IT industry shift fighting to be the standard. But I would like to ask if you can help me continue writing the code to migrate multi-line expense checks. This I would appreciate very much.
    Regards
    Sol

    ReplyDelete