Why Import QuickBooks Tables w/QODBC Article

This post explains why to use QODBC to see fields not available to you in QuickBooks®.

A database table looks like a spreadsheet with columns and rows. Each field is a column. In a customer table, for example, the column heading contains field names like: Name, Phone Number, Email, etc.

Each record is a row. In a customer table, for example, each row is a customer.

QODBC makes more fields (or columns) available to you than either QuickBooks® reports or QuickBooks® export feature.

To read more about the difference between IIF files and QODBC imported tables, see this article:

            Import QuickBooks Tables: IIF vs. QODBC
            www.vbquick.com/2011/10/import-quickbooks-table-iif-or-qodbc.html


The article also contains screenshots of IIF tables and QODBC imported tables for comparison. There you will see how many more fields QODBC makes available to you.

For a Visual Basic function to import tables and their properties using QODBC, see this post:

            QuickBooks Tables in Access w/ QODBC Form
            www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html


When using Microsoft® Access to read and write QuickBooks® data, you need the field names and their properties.

For example, to write a QuickBooks® check from your Access database, you need the field names for Vendor, Check Number, Account, etc.

You also need to know what you can do with each field:
  1. read (property name: queryable)
  2. insert (property name: insertable)
  3. edit (property name: updateable)
  4. null (propery name: nullable)
  5. length (property name: length
  6. type (property name: type, typename or datatype)
These properties have changed in the past with newer versions of QuickBooks®. That is one reason you need to access them; to be aware of changes. The details below may not apply to the version of QODBC and QuickBooks® that you use; however, the principle applies. For example, a field previously not insertable may become insertable in the future, so the ability to access this changing information is advantageous.

Let us examine these properties. Remember, the details may change but these examples should give you an idea of the necessity of accessing these properties.

Queryable

I have not checked the queryable property of every field in every table with QODBC but I have yet to see a field whose Queryable property is false. To be safe, I still access and use them.


Editable

You may wish to edit information using QODBC. For example, you may use The Link Principle (available at Amazon.com) to create memos that link transactions in QuickBooks® to receipts on your computer. You may then create a form that lets you select a QuickBooks® transaction from a list and open its receipt with a mouse click.

You need to know what fields are Editable. For example, you may create a new deposit and insert the date using QODBC; however, you may not edit the date of an existing deposit. You may only edit the dates of existing deposits in QuickBooks®.


Insertable

To create a new check in QuickBooks®, you need to know which fields are Insertable. For example, if you try to insert Amount into a check, you will produce an error. You may only insert Amounts into checks through expense lines or item lines. QuickBooks® adds up the expense and item line Amounts to calculate the check Amount.


Nullable

To create a new check in QuickBooks®, you may ask for user input through a form. If your form allows null values for fields, you may produce errors. Not all fields are nullable. For example, to make sure you select an Account when writing a check, QuickBooks® does not allow a null value for AccountRefListID or AccountRefFullName. The good news is that you need only provide one and the other is automatically written.


Length

To edit or insert data to any field, you must limit the data length to the length QuickBooks® allows. For example, if you try to insert or edit a Reference Number with twelve characters for a check, you will produce an error. The field length of RefNumber in the Check table is eleven. You must set the lengths of fields in user input forms to the QuickBooks® allowed length to avoid errors.


Type

To edit or insert data to any field, you must insert the right type of data. For example, you cannot insert text into a number or date field. You may use any of these properties to determine the right type:
  1. type
  2. typename
  3. datatype
Use one of the above type properties to limit user input to the proper type of data for the field in your database.



Summary

When programming a database to insert and edit QuickBooks® transactions with QODBC, your queries must not allow insertion and editing of information whose:
  1. corresponding QuickBooks® field properties are set to False (0)
  2. length exceeds the length allowed by QuickBooks®
  3. data type is not the same as the QuickBooks® required data type

Accessing and using these properties in your programming is a must. Make sure your user input forms allow the proper input to avoid errors.

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