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:
- design the form any way you like
- add additional functionality
- adapt it to a network or server
- incorporate it into another Microsoft® Access database
- 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:
- uses Pass-Thru queries to retrieve:
- the list of QuickBooks® tables and their corresponding descriptions
- the Columns table for any QuickBooks® table (columns, field names and properties)
- fills the first listbox with the list of QuickBooks® tables
- fills the second listbox with the Columns table for the selected table from the first listbox
- puts query results into Microsoft® Access tables
- 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.
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:
- sp_Tables: The Table of all table names. This main table is a list of all the tables, for example:
- sp_Columns: The above tables' field names and their properties. The Customer columns table, for example, would contain:
- Rows (Field Names)like:
- Columns (Field Properties)like:
- update a field that is not updateable
- insert a field that is not insertable
- insert or update the wrong data type into a field of another data type
- insert or update the wrong precision for a number field
- insert or update text of a length exceeding the QuickBooks® length limit of a field
- 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:
From top to bottom and left to right, create controls and name them as follows below: control: name:
- Command Button: cmdReloadTables
- Label: lblRemarks
- ListBox: lstTables
- Command Button: cmdReloadColumnsTable
- ListBox: lstFields
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.
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:
- double-click the listbox (in Design View) to open the Properties Window
- Make sure you are on the Format tab
- Make sure lstFields appears in the drop-down box at the top of the Properties Window
- 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...
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: