How to Migrate Transactions from Microsoft Access to QuickBooks Part 2

This is a two part tutorial that begins here.


Step 4: Do Not Use Billable Status

Some account types do not allow Billable Status so do not use that field.

For example, if you write a check as a credit card payment, Billable Status is not allowed. If you try to insert Billable Status to a credit card payment check, an error stops the code and the data is not inserted.

To confirm this, create a test check to pay against a credit card and try to select a Billable Status. You cannot.





If you need to migrate Billable Status, include a function that adds or removes that field and value in your SQL according to the Account Type in the CheckExpenseLine.

ASAP I will post this function. Contact me if you need it sooner.



Step 3: Entities and Accounts Must Already Exist

You must make sure Vendors, Customers and Accounts already exist in your QuickBooks® file.

Vendor/Customer

For example, if your table contains a Vendor named ABCStore but this vendor does not exist in your QuickBooks® file, an error will occur and the data will not be inserted.

Additionally, if your table contains a Vendor named ABCStore but your QuickBooks® file calls this vendor: ABC Store (with a space between the ABC and Store), an error will occur because that is the same as that Vendor not existing in QuickBooks®.


Account

The same goes for accounts. You must match the account names exactly as they appear in your QuickBooks® file.

For example, let us say your table account name is Liability Insurance. The matching QuickBooks® account name is the same so you should have no problem, right? Not necessarily. You must match the Full Name. If Liability Insurance is a subaccount of Insurance, the fullname will be: Insurance:Liability Insurance.

Use VBDemo to look up your account names if necessary or their ListIDs by typing this into VBDemo:

     select listid,fullname from account

You may use either the ListID or the FullName. You do not need both.

You cannot use the Name field from the Account table because the line tables (CheckExpenseLine, CheckItemLine, BillExpenseLine, etc.) do not contain that field. The line tables only contain the AccountRefFullName field.


How to Use the Code


1.  Use a copy of your QuickBooks® file until you are satisfied this works for you.

2.  Copy and paste the code into a new module.

3.  Make sure QuickBooks® is open and you have granted QODBC permission to access it.

4.  Match table field names and remove billable status.

5.  Make sure you have included all the necessary data. For example, if you do not include PayeeRefFullName or PayeeRefListID for CheckExpenseLine transactions, your checks will have no Payee.

6. Write to the LINE tables: CheckExpenseLine, InvoiceLine, DepositLine. Use VBDemo to find the corresponding line table for the table you want to write to.

7. Open the Visual Basic Editor and the Immediate window to see the SQL statements as they are written. This gives you excellent examples of the SQL and how to construct it. Additionally, if you run into trouble, you can copy the SQL from the Immediate window and paste it directly into VBDemo for more information on any errors. What kind of errors? Errors like maybe you included a non-insertable field by mistake (Amount is the most common) or your vendor does not exist in QuickBooks or you included BillableStatus on a credit card payment. The code works but you must have followed the instructions on field names, insertable fields and BillableStatus.









8. To watch the code in action, enter some checks with your check register open so you can see them being written.

9. Type fncWriteData into the Immediate Window of the Visual Basic Editor.

10. You will be prompted for the name of the table from your database that you want to migrate.

11. You will be prompted for the QuickBooks® table name to write to. Remember, the corresponding LINE table of the transaction type you are migrating.

Let me know how this works for you.

I use this with QuickBooks 2009 Premier Contractor, Access 2002 and QODBC Version 9.





Continue to Part 3 for help on testing the function if you run into problems:
http://www.vbquick.com/2012/03/migrate-quickbooks-microsoft-access-2_23.html

Comments

  1. I am getting an error at qb.execute. Any idea?

    ReplyDelete
  2. Could be an number of issues. Are any of the SQL statements going through?

    Things to look for:

    1. Use Vendor, Customer and FULL Account names that already exist in QuickBooks. Mispellings are interpreted as non-existing names in QuickBooks and will produce errors.

    2. Check your table's field lengths against QuickBooks field lengths. For example, RefNumber cannot exceed 11 characters.

    3. Check that all required fields are used. Use VBDemo and the column Required_On_Insert for required fields.

    4. Make sure to insert to the correct table, ie.: for checks, checkexpenseline or checkitemline, for bills, billexpenseline, etc.

    5. Use VBDemo to confirm that all the fields you are trying to insert are insertable. A common error is using Amount instead of ExpenseLineAmount.

    6. Use the same data types. Trying to insert text into a number field will produce an error. Double check your table field types against their corresponding QuickBooks field types.

    What is the err.number and err.description?

    Pasting the SQL into VBDemo should give you more information on the error.

    ReplyDelete
  3. Any chance the multiple item posting is going to be added? Looking to do multiple line invoices.

    ReplyDelete
  4. Hello,

    Unfortunately I don't have access to Access, can this be done with Excel?

    Thanks,

    Scott

    ReplyDelete
  5. QODBC Driver does work with Excel, however, I have not used this code in Excel. You can download a free trial version of QODBC and see if it works for you before buying the product. I have never used it with Excel.

    ReplyDelete

Post a Comment

Popular Posts