QuickBooks® - QODBC Quick Start Part 7 for Beginners

This is part of a tutorial on how to create a Microsoft® Access customer database with a live link to QuickBooks® using QODBC with copy and paste code, screenshots and instructions. The tutorial begins here.

Overview

The previous post explained:
  1. how Microsoft® Access reacts when you try to overwrite a table used as a record source.
  2. how to incorporate Visual Basic code that:
    1. clears the form's record source to avoid errors when recreating the table
    2. calls a function that recreates the form's record source

Record Source Function

Copy this Function and paste it into the Visual Basic Editor of your form.

Remember, the reason for this function is to change the form's record source from anywhere in your form's code by typing: fncForm_RS.

Hint: The _RS extension of this function's name tells you it changes a record source. Coding functions and procedures with extensions let's you find them quickly when reprogramming or debugging.
Function fncForm_RS()
'if an error occurs, go to the error line to report it
On Error GoTo Form_RS_err
'set the form's record source to the reloaded customer table and orders the customer alphabetically by name
Form.RecordSource = "select * from tblCustomer_reloadable order by name"
'return to the procedure
Exit Function
'come to this line if an error occurs
Form_RS_err:
'print information about the error in the immediate window
Debug.Print Err.Number & " " &; Err.Description
'display the error to the user in a message box
MsgBox Err.Number &; " " &; Err.Description
End Function

Now if you learn more VBA and want to change the record source, you only need to change the Function. This habit of creating separate functions or procedures for record sources saves time.

For example, you may decide to program a control to search for customers. In that case, you would rewrite the record source function to use criteria. Then you would call the function for each search. If this is beyond you, do not worry. All you need to do is copy and paste. If you read the green notations, you will also learn as you go.


Test the Button

Here is a fun way to test your new button.
  1. Open the customer center in QuickBooks®.
  2. Create a new test customer with some fake information. Hint: Begin the customer name with an asterisk (*) to make it appear as the first customer on your form.
  3. Return to the Microsoft® Access database and click the reload button.
  4. Look for the new test customer.
  5. Return to QuickBooks® and delete the test customer.
  6. Return to the form and click the reload button again to see that the customer is no longer there.
Notice that during the time the form's record source is set to nothing, #Name? appears in all the text boxes instead of data as illustrated below.





After the form's record source is reset, data appears in the text boxes again.

If you did not begin the test customer with an asterisk, you may need to scroll the form to find the test customer.



You now have a Microsoft® Access customer database with a live link to QuickBooks® using QODBC. You simply refresh customer data with the click of a button.

If you haven't redesigned the form to your liking, you may do that now. That is the beauty of doing your own programming.

Hint: I like to use pretty colors and fun clip art to keep work interesting.


Remember, the main reason for creating a customer form in Microsoft® Access is:

  1. to provide you with as many additional customer fields as desired
  2. to create a contact table so that each customer may have as many contacts as needed.

Next

Next you will learn how to add additional fields and link them to your customers. There are two reasons for using QODBC:
  1. a quick, live link without the need for importing IIF files or Microsoft® Excel spreadsheets
  2. QODBC gives you access to QuickBooks® ListIDs that make excellent unique identifiers because:
    1. QuickBooks® assigns them
    2. they are unique
    3. they are permanent.
Your database will use these ListIDs to link customers to additional records in your database.


Continue to the next post… or return in a day or two for the next installment. Check http://www.twitter.com/vbquick for news and updates.

Why Doesn't Application.filesearch Work

First, a couple of reasons why my databases still run on Microsoft® Access 2002 and then how to handle application.filesearch.

My computers and laptop contain many databases using Microsoft® Access 2002. A large percentage of these databases use QODBC to exchange data between Microsoft® Access and QuickBooks®. If you are busy like me, you know the value of not fixing what is not broken.


The Domino Effect

If you are a developer or use VBA with Microsoft® Access or Visual Basic, you know about the domino effect. If this phrase as it applies to programming is unfamiliar to you, consider yourself lucky. How simple a task it seems to improve a small feature of a database you wrote yourself. And how often that tiny improvement ends up the source of bugs that multiply exponentially.

I have spent many frenzied sessions long into the night repairing what had once been a beautifully functioning database simply because I chose to make a small improvement without considering the ramifications.

If you cannot relate to this scenario, then congratulations! You are either the most excellent programmer on the face of the earth or have an iron will that allows you to resist the temptation to improve a complicated, functioning database.

The application.filesearch function is a huge part of my file linking code as explained in The Link Principle. For reasons previously stated, I am not about to rewrite all my databases. Maybe someday, but not today. Or tomorrow. Or even this year!


Quick Fix

If you use Microsoft® Access 2002 and have installed Microsoft Windows Search 4.0 and do not wish to rewrite all your code, you may use a shortcut to turn the indexer on and off as explained in this post: Windows Search 4.0 interferes with application.filesearch (http://www.vbquick.com/2011/09/windows-search-40-interferes-with.html)

At the time of this writing, I still use this temporary fix on most of my databases because I dread, dread, dread re-writing the programs I use daily. Working without these programs would be like raking my yard with a fork.


Permanent Fix

If you'd like to replace Application.Filesearch with vbDirectory, you might consider this code on my sister site TheGenericDatabase.com: 
     
            Replacing Application.Filesearch with vbDirectory for List Box Rowsource of Files
            www.thegenericdatabase.com/2011/11/file-list-vbdirectory-vba-visual-basic.html


I re-wrote one of my programs using the above method and was surprised to find two interesting improvements:
  1. speed - vbDirectory is faster
  2. placing file names into a table to use as the source of a list box removes the size limit imposed by using a value list of file names - if you do not understand this, no matter, just know that it is way better


FileSystemObject

You can also use filesystemobject to grab file lists and some of my older programs do use it. However, vbDirectory does not require any additional libraries. The less library references you can get away with, the faster your databases will run.


More

For more information on this topic, see: http://support.microsoft.com/kb/935402.

I am not the only one disappointed with the application.filesearch issue. I have heard enterprise programmers complain about their databases being broken after installing Microsoft® Access 2007 and discovering filesearch no longer works.

I hope Microsoft adds application.filesearch back to the next new-and-improved Microsoft® Access. Until then, I'm happy with everything just the way it is.

QuickBooks® - QODBC Quick Start Part 6 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks®. The tutorial starts here.


Review

The previous post explained how to:
  1. create a Command Button for the Customer Form
  2. name the button
  3. create an On Click event
  4. open the Visual Basic Editor

Overview

Let us refresh for a moment where you are in the creation process. If you do not understand the terminology or exactly what you are doing, no matter. Understanding is secondary. Following these instructions produces results without understanding.

  1. You copied and pasted fncCustomerTable into your database. This function connected your database to QuickBooks® using QODBC and then created a table from the results.
  2. You tested the function by typing fncCustomerTable into the Immediate Window of the Visual Basic Editor.
  3. When you created the customer form, you chose the customer table as its record source. Remember the New Form Wizard where you chose a table? If not, that is fine.
  4. Instead of typing fncCustomerTable into the Immediate Window every time you need to refresh the customer table, you will now program a button to click. The button will use fncCustomerTable to reload customers into the database for you.



Overwriting a Form's Record Source

The function you created overwrites the table: tblCustomer_reloadable. You used a suffix (_reloadable) as a descriptive code. Coding tables, queries and functions provides clues for working on your database in the future. A suffix like _reloadable lets you know you may safely delete the table because your database contains code to re-create it.

Let us examine what happens when you try to overwrite a form's record source.

Between the lines Private Sub and End Sub, type:
fncCustomerTable





Close the Visual Basic Editor and select Form View from the View Menu of the Microsoft® Access menubar. Save the form if you have not already done so.



Test the Button

Click the Command Button and wait while Microsoft® Access runs the function and displays the error message below.

Whenever you see this message, you know that Microsoft® Access tried to overwrite a table that is currently a record source for an open form, report or control. Microsoft® Access will not overwrite a table in use. We will fix this.





Fix the Button

You could be lazy and fix this by:
  1. clearing the form's record source as on line 100
  2. running the function as on line 110
  3. resetting the form's record source as on line 120.
Private Sub cmdReloadCustomers_Click()
100     Form.RecordSource = ""
110     fncCustomerTable
120     Form.RecordSource = "tblCustomer_reloadable"
End Sub

However, in the future you may create forms that use complicated queries or SQL statements for record sources. You may also need to recreate the form's record source using criteria that change by user selection. Do not let this confuse you, just continue and we will fix it properly.



A Better Fix

The better method is to:

  1. write a Function that creates the record source
  2. clear the form's record source  
  3. perform the activity
  4. call the Function to re-create the form's record source

To accomplish this, delete fncCustomerTable from the code above and replace it with the code below. Be sure to keep the beginning of the procedure and the end of the procedure (Private Sub and End Sub)

'make the mouse pointer an hourglass
100     Screen.MousePointer = 11
'clear the form's record source
110     Form.RecordSource = ""
'run the function that re-creates the customer table
140     fncCustomerTable
'call the function that re-creates the form's record source
150     fncForm_RS
'make the mouse pointer an arrow
160     Screen.MousePointer = 0



Your Visual Basic Editor should now look like this:




Notice line 150 says: fncForm_RS. This is where Visual Basic leaves the button-click procedure to re-create the form's record source. We will write this function next. It is shorter than the one you just wrote so you are only minutes away from importing customers with a click.

Continue to the next post.

QuickBooks® - QODBC Quick Start Part 5 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks®.

The tutorial starts here.


Review

Previous posts explained how to:
  1. create a Pass-Through query to;
  2. import the QuickBooks® customer table into;
  3. a Microsoft® Access database and;
  4. create a new customer form and;
  5. fill the form with the imported customer data.
This post explains how to create a Command Button on the form to refresh customer data directly from QuickBooks® without using IIF files, spreadsheets or linked tables.




The Command Button

Running a Pass-Through query each time you open the customer form wastes time. You need a way to refresh customer data only when desired.

Your form may look different than these illustrations but no matter. For example, ListID and IsActive do not appear in these illustrations in Form View. I have moved them off the form because although queries require them, viewing them is unnecessary.

To avoid tabbing to fields you do not use or fields located off the form:

  1. Select the field's control in Design View
  2. Double-click the control to open the Properties window
  3. set the control's Tab Stop property to No.






You may have chosen different fields for your form than shown here. The fields you include are up to you but remember you must include ListID.

With the form in Design View, expand the form header to make room for a Command Button.

Create a new Command Button by selecting the Command Button icon from the Toolbox Menu. Hint: the Toolbox Menu appears on the Microsoft® Access menubar when your form is in Design View. If you do not see the Toolbox Menu:
  1. Select View from the menubar
  2. Select Toolbars from the View menu
  3. Check Toolbox on the Toolbars menu






After placing the new Command Button on the form, click Cancel if the Command Button Wizard appears.




Name the Button

Double-click the new Command Button to open the Properties window.

Click the Format tab and type the Text you wish to appear on the new Command Button to indicate its function. You may use any words you like. Some suggestions are:
  • Reload Customers
  • Refresh Customers
  • Get Customers
  • Re-query Customers
  • Connect to QuickBooks
  • Import QuickBooks Data




Click the Other tab to name the new Command Button. This name will appear in the Visual Basic Editor so assign the Command Button an easily recognized name.

The preferred method for naming controls is to use a prefix. One generally accepted prefix for Command Buttons is cmd. Prefixes are known as VBA naming conventions in programming circles. That is a fancy way of saying: a set of rules for naming variables so you know what the variable represents when you see it.

You may use VBA Naming Conventions or design your own, so long as you know what the prefixes mean. This insures that you can find command buttons in your code by searching for cmd if you decide to add or change code later.



Create an Event

Click the Event tab and place your cursor on the line next to On Click. You will create an On Click Event. You will tell Microsoft® Access what to do when someone clicks the new Command Button. Clicking is an Event. A much smaller event than a concert or football game but an event just the same.

When you place the cursor in the text box next to On Click, three dots appear at the end of the text box. Click the dots to open the Visual Basic Editor.






The Visual Basic Editor creates a Procedure. Do not let the word Procedure intimidate you. Procedures are simply blocks of statements and nothing more.

Procedures begin with Private Sub plus a name. In this case, the name is the name you gave the Command Button plus the suffix: _Click(). This translates to Visual Basic as:
When a user clicks the Command Button with this name, come to this Procedure.

Procedures end with the words: End Sub to tell Visual Basic where to stop.

Between Private Sub and End Sub, we add instructions for Visual Basic to follow.

We want to refresh the customer table with a button click. We already wrote a function to accomplish this so now we only need to tell Visual Basic to perform that function.




Continue to the next post

QuickBooks® - QODBC Quick Start Part 4 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks®. The tutorial starts here.



Review

The previous post explained how to:
  1. create a new module
  2. write a Function that creates a Pass-Through query that returns records from the QuickBooks® customer table
  3. create an SQL statement that places the query results into a Microsoft® Access table
  4. navigate to the database window to see the new table


Examine the New Table

Double-click the table to view the results if desired and then close.

With the new table highlighted, click Design from the menubar of the database window.




Design View lets you see all the fields in the customer table. The list is so long the screenshot does not contain all the fields. You will seldom need all the fields from any table. However, now you know how much more information QODBC provides than the QuickBooks® interface.




Create a Customer Form

You may now create a form that uses the new table as a record source.

  1. Select Forms from the Objects list of the database window.
  2. Click New from the menubar of the database window
  3. Select Form Wizard from the New Form window
  4. Select the new table from the drop-down list





Highlight the fields you wish to include on your form and click the arrow to move them to the Selected Fields column.

Include ListID because remember, ListID is the Unique Identifier. You will use each customer's ListID to link to other tables.





Select Tabular as the type of form to create.





Select the style you like.





Type a name for the new form and click OK.



Design Your New Customer Form

You now have a form filled with QuickBooks® customers. You may alter the appearance to suit your taste. For example, ListIDs are for reference only so you may wish to move them off the form or make them smaller.




To alter the form, select Design View from the View menu of the Microsoft® Access menubar. Design the form any way you like and save it.




Next you will create a button on the form that refreshes the customer table with a mouse click.

Continue to the next post

QuickBooks® - QODBC Quick Start Part 3 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks. The tutorial starts here.


The last post explained how to create a Pass-Through query that returns results from QuickBooks® but what can you do with this information?


Now What?

We worked with the customer table because it generally contains less records than the transaction table. Now that you satisfied the need for a quick display using the QODBC driver, we can explore the possibilities.

One advantage of using QODBC is that you can add fields in Microsoft® Access that QuickBooks® does not provide. For example, you may desire to keep records for employees that are either confidential or simply extended. Some possibilities for extra fields for notes or other data:
  1. Extra information on employees, vendors or customers. QuickBooks® provides one note field per record. Database tables let you add multiple, dated notes for each employee, customer or vendor.
  2. Notes, extra fields or paperwork for drivers.
  3. Pictures, notes and extra fields for fleet vehicles.
  4. Contacts for companies. You may have as many contacts as needed for each QuickBooks® company.

Unique Identifiers

Slowing down to read this explanation may bore you. No one can stop you from skipping ahead and playing some more with QODBC. However, Unique Identifiers are one of the building blocks of database design so a basic understanding will help you in the future.

You need Unique Identifiers to link QuickBooks® records to Microsoft® Access records. You may use Social Security numbers for employees but the security risk is unnecessary. QuickBooks® creates ListIDs for each record. These ListIDs are Unique Identifiers because:
  1. No two ListIDs are the same
  2. QuickBooks® creates them
  3. QuickBooks® never changes them
  4. No one else can change them
This makes ListIDs perfect Unique Identifiers to link QuickBooks® records to Microsoft® Access records.

If you did not understand that do not worry. Just know that you will use ListIDs to link QuickBooks® records to your Microsoft® Access records.



How to Use Queries

Running a query on the entire customer table takes time and customer records may not change often enough to warrant running a query each time you need customer information.

Remember, the beginning of this tutorial explained that QODBC is best used when limiting results using criteria.

We accomplish this by creating a customer table in Microsoft® Access and only refreshing the table when necessary. Remember, ListIDs never change.

Instead of querying the entire customer table, we can choose a customer from the Microsoft® Access table and query updated information for that customer only. This accomplishes three things:
  1. saves time while still providing the most up-to-date information for one customer
  2. lets you access customer information quickly via the database when QuickBooks® is closed
  3. lets you transfer your customer list to a thumbnail drive in Microsoft® Access or Microsoft® Excel.

The previous post explained how to create a query using an SQL statement to display customer information.

Next we will put that SQL statement into a Function that you can use to refresh the customer table any time you want. Writing Functions lets you create SQL and forget about it. The Functions do all the work for you.


Create a New Module

Create a new module and set the references that are checked below.

If you do not know how to create a module or set references, see The Generic Database:

            Chapter 01: Part 2: How to Create a New Module and Select References
            www.thegenericdatabase.com/2011/09/03-references.html




Select the new module by double-clicking it from the database window:



Create the Function

Copy and paste this code into the new module. Do not include the asterisks at the beginning and end.

The comments (in green) help you learn what the Function does.

'***************************
Option Explicit
Function fncCustomerTable()
'go to the error routine if an error occurs
On Error GoTo fncCustomerTable_err
'declare variables
Dim db As DAO.Database, qd As DAO.QueryDef, q As String, t As String
'name the query
q = "qryTemp"
'name the table
t = "tblCustomer_reloadable"
'set the db variable to the current database
Set db = CurrentDb
'set the qd variable to a query def
Set qd = db.CreateQueryDef(q)
'set the query to return records
qd.ReturnsRecords = True
'set the timeout in case the query hangs - increase this if you have lots of records
qd.ODBCTimeout = 60
'set the connections string telling the query to connect to QuickBooks using QODBC
qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
'create the sql telling the query to get data from the customer table
qd.SQL = "select * from customer"
'set the warnings to false to avoid the message of overwriting the table
'you will overwrite the table every time you refresh it
DoCmd.SetWarnings False
'put the information from the query into a table
DoCmd.RunSQL "select *  into " & t & " from " & q
'set the warnings back to true
DoCmd.SetWarnings True
'come back to this line from the error routine to set qd and db back to nothing and delete the temporary query
fncCustomerTable_exit:
'delete the temporary query
DoCmd.DeleteObject acQuery, q
'set the variables back to nothing
Set qd = Nothing
Set db = Nothing
'exit the function
Exit Function
fncCustomerTable_err:
'deletes the temporary query if it already exists
If Err.Number = 3012 And InStr(Err.Description, q) > 0 Then
DoCmd.DeleteObject acQuery, q
Resume
End If
'displays a message box if QuickBooks is not open
If Err.Number = 3151 Then
    MsgBox "QuickBooks is not open."
    end
End If
'prints the error information to the immediate window
Debug.Print Err.Number & " " & Err.Description
'Displays a message box with the error information for the user
MsgBox Err.Number & " " & Err.Description
End Function
'************************


Run the Function

To run the function, type:
fncCustomerTable
in the Immediate Window.

Hint: If you do not see the Immediate Window or if you do not know where the Immediate Window is, see this post: Immediate Window.

Allow ample time for QODBC to connect to QuickBooks® and retrieve data. This varies according to how many customers your file contains. Remember, you will only refresh this table when new customers are added or their names change. The ListIDs always remain the same.

When the mouse pointer no longer shows an hour glass, your table should be complete.

Navigate to the database window. If you do not know how to do this or you do not see the database window, see The Generic Database:

            Appendix A: Hide & Unhide the Database Window
            www.thegenericdatabase.com/2011/09/appendix-where-did-my-database-window.html


Select Tables from the Objects list and your new customer table should be listed.


Next we will examine how to use the information in the table.



QuickBooks® - QODBC Quick Start Part 2 for Beginners

This is part of a Quick Start tutorial on using QODBC with QuickBooks. The tutorial starts here.

After telling Microsoft® Access that this will be a Pass-Through query, you must designate the source from the properties window. If you forget, Microsoft® Access will simply ask you for the source by displaying the Select Data Source window illustrated below. This makes it easy to choose QuickBooks® for your query without writing or remembering anything.




Click the Machine Data Source tab, select QuickBooks® Data from the list and click OK.

This tool is handy when you can't remember the data source string. However, this window will then appear every time you run the query. I will provide you with a better alternative.




For now, you will type the data source string in the properties window of the query. To do that, select View from the menubar and Properties from the View menu.






Type:
ODBC;DSN=QuickBooks Data;SERVER=QODBC
on the ODBC Connect Str line of the Query Properties window and close the window (highlighted text below).

Do not worry that you will have to remember this odd string of words because we will automate this with a function later so you no longer need to type it or remember it.





Choose a QuickBooks® Customer

Go to your QuickBooks® customer center and copy the name of one of the customers. We will create your first query to select all the data from the customer table for that customer.

Type:
select * from customer where name='customer name'

into the Query window. Do not type customer name inside the apostrophes. Replace customer name with the name of your customer. Keep the apostrophes.

Important: Copy the name from the Customer Name text box of the Edit Customer window as illustrated below because that is the field designated as Name in the customer table. This is imperative when you enter different names in the Company Name text box and Customer Name text box in QuickBooks®. This is why you need to know the names of the QuickBooks® fields and how they correspond to forms and reports in QuickBooks®.

I have a database for you that displays the tables and their corresponding field names but for now just make sure to copy the Customer Name for use in your first query.





Be sure to keep the apostrophes. To keep things simple this first time, do not use a customer whose name contains an apostrophe to avoid the ODBC call failed error.

Run the query either by clicking the exclamation point on the Query Design menu at the top of the Microsoft® Access window or...




...select Run from the Query menu of the menubar.



View Results

The first time you connect during a session or after a long time of inactivity, expect a slight delay while QODBC establishes a connection. Frequent connections thereafter are faster.

Your query should display all the data for all the fields of the customer you chose.



If your query returns results, congratulations! If not:
  1. Make sure the name between the apostrophes is the same as the string in the text box of the Customer Name in the Edit Customer window
  2. Make sure you closed the Edit Customer window before running your query.
  3. Make sure you did not include a space after the first apostrophe or before the last apostrophe
If your query still does not produce results, retype it as follows:
select * from customer where name like '%customer name%'
That means keep the apostrophes and percent signs and replace customer name with the name of the customer. 

Next you will copy and paste code that places query information into a table for you and then you will create a button so all you do is click the button to create or refresh the customer table. Are you excited? Did you ever think it would be this easy? Let's go!

QuickBooks® - QODBC Quick Start Part 1 for Beginners

Overview
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

QuickBooks PriceLevelPerItem Part 2: Import into Microsoft Access Table Using QODBC

This is part of a tutorial on how to transfer the Per Item Price Level table from QuickBooks® into Microsoft® Access using QODBC without IIF files or spreadsheets. Click here to start at the beginning.

The Relationship Between Items, Prices and Jobs

The Design View query below illustrates the relationship between the following QuickBooks® tables:
  1. Customer
  2. PriceLevelPerItem
  3. Item
You need not understand these relationships to benefit from the copy and paste code included in this post.

These detailed explanations are for:
  1. the curious
  2. those who wish to alter the code
  3. devleopers interested in QODBC and its application
The relationships are as follows:
  1. the Customer (job) Table contains the ListID for its PriceLevel: PriceLevelRefListID
  2. the PriceLevelPerItem Table (center) contains ListIDs for its items: PriceLevelPerItemItemRefListID





The above query (design view) would return records similar to the results below that show:
  1. Customer Name from the Customer Table
  2. PriceLevel Name from the PriceLevelPerItem Table
  3. Item Name from the Item Table
  4. Item Description from the Item Table

Again, do not be concerned if you do not understand this. These explanations are for the those who desire a deeper understanding of QuickBooks® tables and how QODBC uses them.



Altering the Function

Depending on your needs, you may alter this function to import price level prices for:
  1. one price level at a time and reload the table every time you need to work on a different price level
  2. all price levels at once and reload the table only when PriceLevelPerItem records change



Real World Applications for the Imported PriceLevelPerItem Table

Reasons for importing QuickBooks® PriceLevelPerItem prices into Microsoft® Access vary but you may wish to use Microsoft® Access to:
  1. create an invoice entry program better suited to your needs
    1. Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices (www.vbquick.com/2011/10/qodbc-quickbooks-invoice-access.html)
  2. create an invoice entry program that hides prices from the data entry technician
  3. compare previous job prices in a report for help in preparing a bid
  4. compare the difference between bid prices you are contemplating and previous jobs' prices
  5. apply prices from one job to another job's invoices to see how they would have affected profit
  6. use union queries
  7. use QuickBooks® unique identifiers (PriceLevelPerItem ListIDs are unique for each price) to link PriceLevelPerItem records to records in a database table.
You may think of other reasons to import prices from the PriceLevelPerItem table. Each business has different needs.

Please note that you will need to select the following references from the Visual Basic Editor:
  1. Visual Basic for Applications
  2. Microsoft® Access 10.0
  3. Microsoft® DAO

If you do not know How to Select References see The Generic Database:

            Chapter 01: Part 2: How to Create a New Module and Select References

            www.thegenericdatabase.com/2011/09/03-references.html

Copy the function below, everything between the two lines of asterisks (do not include the asterisk lines).

Paste the function into a new module. If you do not know how to create a new module, the post directly above on How to Select References also contains instructions on creating a new module.

If you do not normally follow my posts and use my codes, you will find references to several other functions.

These functions save time and you should have them in all your databases. They are short. Put them in a separate module so you can quickly import them into other databases for your convenience.

If you do not already have them, copy and paste these also:


fncDeleteQuery:

fncDeleteTable:

fncWriteError:

QODBCConnect


The following function contains detailed explanations of each line. You do not need to understand the function. The explanations provide an excellent learning opportunity.



Copy Paste Function

*************
Function ReloadPriceLevelPerItemPrices()
        'if an error occurs, go to the error line that writes error information to
        'a message box for the user and the Immediate Window for the programmer
On Error GoTo ReloadPriceLevelPerItemPrices_err
        'declare variables for database, QueryDef and the query name
10      Dim db As DAO.Database, qDef As DAO.QueryDef, qName As String
        'assign the name of the query to the string variable
20      qName = "qryPT_PriceLevelPerItemPrices"
        'call a function that deletes the temporary query if it already exists
        'this query first checks to see if one with the same name exists and if it does, deletes it
        'if you try to delete a query that doesn't exist, Visual Basic will present
        'an error message telling you it cannot find the query
30      Call fncDeleteQuery(qName)
        'set the database - assumes you are working in the current database
40      Set db = CurrentDb
        'create the query using the name you assigned above
50      Set qDef = db.CreateQueryDef(qName)
        'QODBCConnect is a function that sets the QODBC connection string
        'this avoids having to remember it every time you need to use it
        'you can simply write "qDef.Connect = " and then type out your connection string if you prefer
60      qDef.Connect = QODBCConnect
        'set the query to return records
70      qDef.ReturnsRecords = True
        'create the sql string and put it into the query
        'you can simple select all fields with: qDef.sql="select * from pricelevelperitem"
        'the more fields you select, the longer the query will take
        'you are better off selecting only required fields
        'limiting the returned records is best done at the query level
        'rather than limiting them when you write the sql that puts the records into the table
        'QODBC takes longer to extract records from QuickBooks than
        'Microsft Access takes to extract records from the Pass Thru query
        'and put them into a table
80      qDef.sql = "select ListID,Name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName, " & _
"PriceLevelPerItemCustomPrice,isActive from PriceLevelPerItem"
        'before you put the query records into a table, you may
        'use a function to delete the table if it exists
90      Call fncDeleteTable("tblPriceLevelPerItemPrices")
        'Turn off warnings so you don't get one for overwriting an existing table
        'You will frequently overwrite this table as well as the query that
        'supplies the data to it because whenever you add a new price or price level
        'in your QuickBooks® file, you will need to refresh the database table
        'you could simply create the pass thru query but if the user deletes it
        'will they know how to recreate it? best to create it programmatically
100     DoCmd.SetWarnings False
        'since you limited the pass thru query to only records that you require
        'go ahead and select them all for putting into the price level table
        'here the user will not know what is going on
        'you may do as I do and open a form telling the user you are extracting
        'data from QuickBooks® and then close the form when you are finished
110     DoCmd.RunSQL "select *  into tblPriceLevelPerItemPrices from " & qName
        'delete the pass thru query because you don't need it now
120     DoCmd.DeleteObject acQuery, qName
        'turn warnings back on
130     DoCmd.SetWarnings True
        'present the user with a message box to signal the price levels are refreshed
140     MsgBox "Finished."
        'use this line to return to from the error routine so if an error occurs
        'qDef and db will be set to nothing
ReloadPriceLevelPerItemPrices_exit:
        'you are finished with qDef and db so set them to nothing
        'if you do not set db to nothing, you may find Access running
        'in your task manager even after you close the database
150     Set qDef = Nothing
160     Set db = Nothing
        'exit the function - you are finished
Exit Function
        'at the beginning of this function, you instructed Visual Basic to come here
        'if an error occurs
        'fncWriteError presents the user with error information in a message box
        'and also writes the same error information in the Immediate Window
        'you may also write fncWriteError to put the error information into a table
ReloadPriceLevelPerItemPrices_err:
Call fncWriteError("", "Functions Prices", "Function ReloadPrices", Err.Number, Err.Description, Erl, "")
        'after writing the error information, return to the exit line to set qDef and db to nothing before
        'exiting the function
GoTo ReloadPriceLevelPerItemPrices_exit
End Function
***************

Continue to the next post