QuickBooks Tables in Access w/ QODBC Form Part 1

The Form

Many ways exist to view the QODBC - QuickBooks® table schema; however, a form that queries the tables with a mouse click is by far the handiest.

This three part tutorial explains how to create a form like the one illustrated below, depending on your particular design preferences.





I chose to provide a tutorial rather than an mde file. Several advantages for you are that you may:
  1. design the form any way you like
  2. add additional functionality
  3. adapt it to a network or server
  4. incorporate it into another Microsoft® Access database
  5. gain a better understanding of Access and QODBC if you are new to either or both


This code was tested with:

QODBC Desktop Read Write 2009 Version 9.00.00.253
QuickBooks®  Premier Contractor 2009
Microsoft®  Access 2002 SP3



What the Code Does

The code used in this form:
  1. uses Pass-Thru queries to retrieve:
    1. the list of QuickBooks® tables and their corresponding descriptions
    2. the Columns table for any QuickBooks® table (columns, field names and properties)
  2. fills the first listbox with the list of QuickBooks® tables
  3. fills the second listbox with the Columns table for the selected table from the first listbox
  4. puts query results into Microsoft® Access tables
  5. displays the description for the table selected in the first listbox



Uses for This Form

Before you can do anything with QODBC, you must know the names of the tables. You must also know the field names available for each table and each field's properties.

For example, to write an SQL statement to retrieve a customer, you need to know the name of the customer table first. Second, you need to know the field names you wish to retrieve to avoid querying all the fields. Querying only the fields you require for your database saves time.



Field Properties

What is a field property? If you are familiar with spreadsheets, a QODBC table resembles a spreadsheet. The rows are the records, the columns are the fields and the cells are the values.

The two types of tables this form retrieves are:
  1. sp_Tables: The Table of all table names. This main table is a list of all the tables, for example:
  2. Account
    Customer
    Employee
    Vendor
    Check
    Deposit
    Invoice

  3. sp_Columns: The above tables' field names and their properties. The Customer columns table, for example, would contain:

    1. Rows (Field Names)like:

      ListID
      TimeCreated
      Name
      FullName
      IsActive



    2. Columns (Field Properties)like:

      ColumnName
      Type
      TypeName
      Updateable
      Insertable
If you use QODBC to update and insert QuickBooks® records like transactions or customers, for example, you need to know some of the properties for each of the fields above to avoid errors like trying to:
  1. update a field that is not updateable
  2. insert a field that is not insertable
  3. insert or update the wrong data type into a field of another data type
  4. insert or update the wrong precision for a number field
  5. insert or update text of a length exceeding the QuickBooks® length limit of a field
  6. insert a record with a null field for a field that is not nullable

For example, if you try to insert or update a value for the FullName field of the customer table, you will produce an error.


Create the Form


To begin, create a form in Design View that loosely resembles this one:






Form Controls

From top to bottom and left to right, create controls and name them as follows below: control: name:
  1. Command Button: cmdReloadTables
  2. Label: lblRemarks
  3. ListBox: lstTables
  4. Command Button: cmdReloadColumnsTable
  5. ListBox: lstFields



Column Widths

Copy these column widths and paste them into Microsoft® Notepad and then copy them from Notepad  into the Column Widths text area of the lstFields ListBox. If you try to paste them directly from the web page, you will produce an error.

.5;.5;.5;.1,5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;.5;

You will change these later to suit yourself but this will get you started so you can see the data.

If you do not know how to do this:
  1. double-click the listbox (in Design View) to open the Properties Window
  2. Make sure you are on the Format tab
  3. Make sure lstFields appears in the drop-down box at the top of the Properties Window
  4. Find the Column Widths line and paste the numbers from above into the text box; right above where the cursor arrow appears on the screenshot below.




You may make both the ListBox and the form wider than what is shown above. The width shown above is narrow to accomodate the web page while allowing original picture size for clarity.

Next, you'll paste the code.


Continue to Part 2...

Comments

  1. can this step by step process be applicable in MySql?

    im creating a program using vb6, and i want it to insert, update,delete and view the Quickbooks database using Qodbc...

    ReplyDelete
  2. Good question!

    Quote from QODBC.com (http://www.qodbc.com/qodbc.htm):

    "QODBC is a fully functional ODBC driver for reading and writing QuickBooks 2013-2002 accounting data files by using standard SQL queries. It requires an ODBC compliant front-end application such as Microsoft Word, Microsoft Excel, Microsoft Access, Visual Basic, PowerBuilder or Delphi. Once installed, it will allow these applications to read and write QuickBooks Accounting files in the same fashion as other file formats installed on your computer."

    ReplyDelete

Post a Comment

Popular Posts