Chuck's Reporting Corner March 2009

Learn about QuickBooks data using the QODBC Driver - the EASY way.

This month we will explore a powerful yet little used tool that comes bundled free with the QODBC driver - called VBDemo.

It is a great device to learn about the basics of the QODBC driver, but can also act as a multifaceted utensil to expose its intricate underpinnings. We teach the use of this tool in our bootcamps, and it is a mainstay in our daily toolbox - even for our experienced programmers.

There are many people who want to know something about the QODBC driver, but are either afraid to dig in, or don't want to mess with Excel, Access or Crystal Reports. So in this article, I am going to show you how easy it is to learn about:

1. What *tables* exist in QuickBooks at the touch of a button
2. The QuickBooks data structure
3. Retrieving actual data

VBDemo - your friendly tool

If you installed the QODBC driver from the FLEXquarters website (www.qodbc.com), go to Program Files>QODBC Driver for QuickBooks>VBDemo

If you installed the QODBC driver from within Enterprise, you can find the tool at %User%/AppData/Local/Intuit/QODBC Driver for QuickBooks. (or do a search on vbdemo32.exe)

Here is the tool. Notice that the buttons are greyed out.

Next click on Connections>Add New Connection. Ensure that you are logged in as Admin and single user mode for the FIRST TIME ONLY, if you have not setup QODBC before.

Choose the Data Source:

Click "OK"

This will pop-up QuickBooks (if you don't already have the QODBC driver installed)

Choose Yes. Select Login as: Admin. (NOTE: This will not affect the permissions while you are logged in to QuickBooks; it only acts as the permission control if you ran QODBC while QuickBooks is not open on the desktop.)

Now the VBDemo tool will appear with the buttons ready for use:

By clicking on the Tables button, you get a complete list of all the "tables"

In this example you would recognize the "Account" table, or the "Check" table. (By the way, this table only includes checks that are NOT applied to bills.)

The "Remarks" column give a brief description of what the table does or contains. In QuickBooks 2009 you will find 132 such "tables".

Now, let's peruse the information - data fields - that exist in a particular table. We do this with a command called sp_columns. The syntax is simple: sp_columns [Table Name]. In this example we typed into VBDemo the following command (without the quotation marks): "sp_columns Account"

Then hit the Query button. You will get a list of the fields that exist in the Account table, and columns that tell your more information about that field.

In this example, we see several columns:

• COLUMNNAME. The name of the actual field. e.g. Account Type, AccountNumber, BankNumber, etc.
• LENGTH. Tells you how long the maximum value of that field is (always wanted to know how long an account name can be? 31 characters; a full name is 159 characters)

Here is another set of columns in the Account table:

(a) RELATES_TO. Tells you what other table the field relates to. e.g. Tax Code table
(b) FORMAT.

• CCCC-MM-DDThh:mm:ssZ - is Time and Date
• %s - is String
• %8.2f - is for numbers with two decimal places
• |1|0|true|false| - is for boolean fields

We won't go into all of the column descriptions here, but if you have a burning question about one of these please e-mail me at askchuck@sleetergroup.com

You can even drag a column within the tool so that you can see the information better. In this example I dragged the "RELATES_TO" column closer to the "COLUMNNAME" column so it was easier to look at, and thus removed the need to scroll back and forth:

The QuickBooks Data

Now let's look at what data is stored in the Account Table:

Replace "sp_columns Account" with "SELECT * FROM Account" (without the quotes). The * refers to all columns. Then hit the "Query Button"

Here is a more detailed description of the columns/fields listed for the Account table mentioned above:

• ListID - Unique identifier for each account name
• TimeCreated - Date and time the Account name was created
• TimeModified - Last time any modifications were made to the Account name (not changes to a transaction, just the actual account information
• EditSequence - not important for Read Only
• Name - Account Name
• FullName - e.g. Payroll Liabilities:Federal Withholding (the name would be just Federal Withholding)
• IsActive - 1 means still active, 0 means inactive
• ParentRefListID - Unique ListID of the parent name.  In the example listed above, the ListID would refer to that of "Payroll Liabilities"
• ParentRefFullName - Name of parent .e.g "Payroll Liabilities" following the example above.
• Sublevel - 0 means the upper most level, 1 the next - al the way up to 4 (0,1,2,3,4 for 5 total levels). So in the example above Payroll Liabilities is level 0, Payroll Liabilities:Federal Withholding is level 1
• AccountType - Used to classify accounts for P&L and B/S
• SpecialAccountType - Accounts generally created by QuickBooks when certain features are turned out, e.g. Sales Tax, Inventory, Payroll; or Undeposited Funds which is the default receive payment repository account.
• AccountNumber - Self explanatory.
• Bank Number - Used for bank accounts, credit cards, other current liability accounts, etc.
• Desc - Account Description
• Balance - Current Balance of the Account
• TotalBalance - This includes the balance for accounts AND their sub account balances.
• TaxLineInfoRetTaxLineName - Tax return line where the account data is selected to be recorded.
• CashFlowClassifcation - used for Statement of Cash Flows (Edit>Preferences>Reports & Graphs>Company Preferences - Classify Cash.

0 Comments