QuickBooks ODBC Database Rules 1

Creating forms in Microsoft Access to use with QuickBooks and QODBC is fairly simple if you stick to a few basic rules. This series contains the basic rules and each post will explain one rule. Are you ready?

Rule One: Use QuickBooks field names in your database tables.

First we'll examine why this makes your life easier and then I'll give you a quick and easy way to apply this rule.

This is the easiest rule to follow and to implement as well. While this rule is easier to apply to a new database, you can still apply it to an existing database by changing your field names.



Why Use QuickBooks Field Names

Using QuickBooks field names makes programming easier because you do not need to map fields.



Mapping Fields Explained

Let us say you have a customer database you want to link to QuickBooks. You would like to import QuickBooks customers into your database as well as insert and update customers from your database into QuickBooks. If you use different field names than QuickBooks uses, you will have to tell your database which table fields go with which QuickBooks fields in all your code.

First, an explanation of how you import data. You import QuickBooks data with pass through queries like this:

      1. write a pass through query that returns records
      2. Write SQL to select data from the pass through query and place it in a database table

If your field names do not match QuickBooks field names, you will forever be writing complicated SQL. The pass through query will remain the same. The SQL that transfers data from the pass through query to your table is where the difficulty arises. If the field names differ between the pass through query and the table, you will have to tell your database which fields from the query go with which fields in the table.




Demonstration 1

Import the Customer Table

The pass through query could be as simple as: "select * from customer". This would simply select all data from the customer table.

The SQL that transfers the query results into the table is where you need to map fields.

For example, let us say your database address fields are:

      Address1
      Address2
      City
      State
      ZipCode

The SQL "select * into YOURDATABASETABLE from THEPASSTHROUGHQUERY" will work to create a new table. To test this, create a new database and select the following references:

      Visual Basic for Applications
      Microsoft Access Object Library
      Microsoft DAO Object Library

Paste the following code into a new module:



With QuickBooks open and QODBC installed on your computer and granted permission to access QuickBooks, type the following line into the Immediate Window of the Visual Basic Editor:

      fncNewTable

Allow QODBC ample time to connect to QuickBooks. A new table is created in your database containing all the customer information in your QuickBooks file.

Open the new table in Design View and look at the field names. Do you see the database field names listed above? No. In fact, QuickBooks lists two types of addresses: one for billing and another for shipping.

Adding information to our customized field names (listed above) requires field mapping. This can be done with code. To illustrate, open the new table and add our custom field names to the table:

      Address1
      Address2
      City
      State
      ZipCode

Next, we'll map these fields to QuickBooks fields using code.



Demonstration 2

Field Mapping in Code

Paste the following code into a new module:





Type the following line into the Immediate Window of the Visual Basic Editor:

      fncAppendTable

Open the altered table and scroll across to the changed fields you created above. Your created fields contain QuickBooks data because the code Mapped the fields like this:

      rs!address1 = !BillAddressAddr2
      rs!address2 = !BillAddressAddr2
      rs!city = !BillAddressCity
      rs!state = !BillAddressState
      rs!zipcode = !BillAddressPostalCode

Imagine having to map fields for the nearly 50 fields in the customer table each time you write code to exchange information with QuickBooks. Imagine doing this for all the other QuickBooks tables.



Quick & Easy Blank Tables


As promised, I will give you a quick and easy way to create new tables for your database using QODBC. First we'll create blank tables for lists and then for transactions.



Blank Tables for Lists


Do not use this code for transaction tables like check, invoice, bill, etc. This code is for list tables like customer, vendor, account, etc. Continue below for code to produce blank transaction tables.

Paste the following code into a new module:




Copy and paste the following line into the Immediate Window of the Visual Basic Editor to create a new blank table:

      fncNewTableFields

Copy and paste the following line into the Immediate Window of the Visual Basic Editor to open the new table in Design View and see the fields:

      docmd.OpenTable "tblCustomers",acViewDesign

HINT: If you do not see the new table, close or collapse the Visual Basic Editor.

Your new table contains all the same fields as QuickBooks.

To create blank tables for other QuickBooks List tables:

      1. Select the text of the code in the Visual Basic Editor.
      2. Select Edit and then Replace from the Standard Toolbar menu of the Visual Basic Editor
            HINT: If you do not see the Standard Toolbar:
                  a. Select View from the menubar of the Visual Basic Editor
                  b. Scroll down and highlight Toolbars
                  c. Select Standard Toolbar from the Toolbars Menu
      3. Make sure the Selected Text radio button is selected on the Replace dialog box
      3. Replace "customer" with the name of the table you want to create. Some table names are:
            a. vendor
            b. entity
            c. class
            d. employee
            e. item
            f. fixed asset
            g. account
            h. jobtype
            i. pricelevel



Blank Tables for Transactions

To create blank tables for transaction tables, use a DateMacro in the code to avoid pulling all the table data into QuickBooks. If you do not use a DateMacro your code could hang or take an extremely long time depending on how many transactions your tables contain. You only want the field names so extracting information is unnecessary. Even if no records exist for today, QODBC will still create the blank table.




To create other list tables, replace check with these table names:

      1. checkexpenseline
      2. checkitemline
      3. invoice
      4. invoiceline
      5. pricelevelperitem
      6. bill
      7. billexpenseline
      8. billitemline
      9. deposit
     10. depositline



Summary

Your table field names should match QuickBooks field names to avoid field mapping each time you write code to exchange data between QuickBooks and Microsoft Access.

Use VBDemo (comes with QODBC) to see field names and properties for use in your code.

When creating forms for user input to insert new data into QuickBooks, use blank tables with QuickBooks fields names. You may quickly create blank tables using the functions above.

Instead of repeatedly opening VBDemo to see tables, fields and properties, you may desire a database form to access the information from inside your database. A tutorial here on VBQuick (QuickBooks Tables in Access w/ QODBC Form Part 1) takes you step-by-step to a finished form that does that for you:

http://www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html

Keeping checking back for the next post in this series for Rule #2.

As always, let me know how this works for you.


Comments

  1. I', so happy that I found your blog. Thank you.

    ReplyDelete
  2. Hi,
    what if i need to do this things in excel i mean want to call values from quickbooks to excel using this above functions
    thanks in advance

    ReplyDelete
  3. 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