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
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
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:
- read (property name: queryable)
- insert (property name: insertable)
- edit (property name: updateable)
- null (propery name: nullable)
- length (property name: length
- type (property name: type, typename or datatype)
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.
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.
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®.
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.
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.
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.
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:
When programming a database to insert and edit QuickBooks® transactions with QODBC, your queries must not allow insertion and editing of information whose:
- corresponding QuickBooks® field properties are set to False (0)
- length exceeds the length allowed by QuickBooks®
- 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.