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.
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®.
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:
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: