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.



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:

2 comments:

  1. Again.. this code will not work b/c of CurrentDb

    how should it be initialized and what value should it be set to?

    ReplyDelete
    Replies
    1. Not sure what you mean. Do you mean you are not using the current database to run this code? You would use the current database to create the query to connect to QuickBooks.

      Delete