QuickBooks® - QODBC Quick Start Part 1 for Beginners

This tutorial provides a quick start for those anxious to see results of using QODBC to import QuickBooks® data into Microsoft® Access.

You will need:
  1. QuickBooks®
  2. QODBC Read Version (or Read/Write Version if you have it) or free trial version
  3. Microsoft® Access

This guide contains detailed instructions, screenshots and copy & paste code to:
  1. connect your Microsoft® Access database to QuickBooks® with QODBC
  2. create a customer form in Microsoft® Access
  3. add a command button that refreshes the QuickBooks® data
  4. extend your QuickBooks® customer data with as many custom fields as desired
  5. extend you customer table with as many contacts per customer as desired
By the end of the second post, you should have created a simple, one-line query that imports the customer table into your database.

What is QODBC?

QODBC is an ODBC driver for QuickBooks®. Do not let ODBC or driver intimidate you.
  • ODBC stands for Open Database Connection--in this case, a live connection to QuickBooks®
  • driver means all the hard work is done so you only need simple statements to communicate with QuickBooks®
You may download the free trial of QODBC to use with this tutorial before deciding whether it is right for you.

How to Avoid the Big Mistake

Many people who download the free trial of QODBC cannot wait to experience the live data exchange between QuickBooks® and Microsoft® Access. Reading documentation bores them. They get around to it eventually but first they want to see action!

Therefore, the first thing newbies do is link a QuickBooks® table to their database and open the linked table. This is exactly the wrong thing to do! If you did this, do not feel bad. I did the same thing at first and so did many other people.

If you did not link a table and open it, congratulations! Suppress the urge and save yourself the disappointment of either a very long wait or a down-right hang.

QODBC does indeed let you link tables from QuickBooks®. However, the table linking method results in a list of linked tables like those below. If you try to open one, you may wait a while for results depending on the number of records the table contains.

Working with linked tables can be time consuming. A faster way exists to access this data that may be easier than you think and requires only one statement.

Warning! If you are not database savvy, the following paragraphs may scare you. Do not run away! If you do not know the meaning of the words in the following paragraphs, no matter. There is nothing mysterious or rocket-scientist about connecting to QuickBooks® with QODBC.

You need not be a computer nerd or know any VBA to follow these instructions. The following explanations are simply for those curious to know definitions and details. They are unnecessary but helpful.

The way to use QODBC most efficiently is to import only the information you need. You do this using Pass-through queries and limit results using criteria. Still with me? Great!

What is a Pass-through Query?

Here comes the boring, extra information. A query is a question. You will be asking QuickBooks® questions like what are the details of a certain transaction or what is all the information you have on this customer?

Pass-through queries are simply questions asked directly of the database server, in this case QuickBooks®, instead of questions asked of linked tables. Pass-through queries eliminate the middle man and deal directly with the source. Aways the better way to go!

You write Pass-through queries using SQL (Structured Query Language). SQL is another intimidating acronym that simply means you must form your questions in a certain way.

I wrote the SQL for you but you will see how simple it is. If you do not know SQL and wish to create your own later, you may do what many beginners do. Many beginners and intermediates use the built-in query design of Microsoft® Access to create queries. After creating the query, they copy the SQL and paste it into their code. Okay, I admit doing the same thing; especially with complicated queries. Hey, it's easier!

Here is a sample of SQL:

select * from customer

This statement means "select everything from the customer table." See how easy this is!

What are Criteria?

Criteria are points upon which you base a decision. For example, whether or not you attend the Valentine's Day dance may depend on criteria such as whether or not you have a date and whether or not you have transportation. Those are two important criteria for a Valentine's Day dance!

When deciding what information you need from QuickBooks®, you use criteria such as date, name, amount, etc. These criteria limit results thereby limiting the time it takes to return results.

For example, let us say the store shelf contains giant bags of jelly beans in mixed colors and also smaller bags of separated colors: green, yellow, orange, etc. If you want only orange jelly beans (yum!) a bag of orange jelly beans provides a shorter route to getting orange jelly beans in your tummy than having to separate orange jelly beans from a giant bag of mixed colors.

Here is an example of criteria:

select * from customer where name ='ABC Plumbing'

The criteria is ABC Plumbing. You asked QuickBooks® to select everything from the customer table but only where the name of the customer equals ABC Plumbing.

Your First Query

With QODBC installed on your computer, either the free trial, the read version or the read-write version, open or create a new Microsoft® Access database and open QuickBooks®.

You should have already set up QuickBooks® to allow QODBC to connect to it. Refer to the QODBC user manual for instructions on how to do this.

You do not have to do anything else like link tables or import linked tables or get external data. None of that. We are cutting out that middle man! That is for amateurs and you are now emulating professionals.

In the database window of Microsoft® Access, select Queries from the Objects menu and select New from the Database Window to create a new query.

Select Design View and click OK.

To change the query type, select:
  1. Query from the menubar
  2. SQL Specific
  3. Pass-Through
You must tell Microsoft® Access this is a Pass-Through query so you can select QuickBooks® as the source. If you forget to choose Pass-Through, worlds will not collide but you will know it when your query fails.

Next you will tell the query what information you want and where to find it.

Continue to the next post


Popular Posts