The last post explained how to create a Pass-Through query that returns results from QuickBooks® but what can you do with this information?
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:
- 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.
- Notes, extra fields or paperwork for drivers.
- Pictures, notes and extra fields for fleet vehicles.
- Contacts for companies. You may have as many contacts as needed for each QuickBooks® company.
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:
- No two ListIDs are the same
- QuickBooks® creates them
- QuickBooks® never changes them
- No one else can change them
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:
- saves time while still providing the most up-to-date information for one customer
- lets you access customer information quickly via the database when QuickBooks® is closed
- 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
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.
'go to the error routine if an error occursOn Error GoTo fncCustomerTable_err
'declare variablesDim db As DAO.Database, qd As DAO.QueryDef, q As String, t As String
'name the queryq = "qryTemp"
'name the tablet = "tblCustomer_reloadable"
'set the db variable to the current databaseSet db = CurrentDb
'set the qd variable to a query defSet qd = db.CreateQueryDef(q)
'set the query to return recordsqd.ReturnsRecords = True
'set the timeout in case the query hangs - increase this if you have lots of recordsqd.ODBCTimeout = 60
'set the connections string telling the query to connect to QuickBooks using QODBCqd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
'create the sql telling the query to get data from the customer tableqd.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 itDoCmd.SetWarnings False
'put the information from the query into a tableDoCmd.RunSQL "select * into " & t & " from " & q
'set the warnings back to trueDoCmd.SetWarnings True
'come back to this line from the error routine to set qd and db back to nothing and delete the temporary queryfncCustomerTable_exit:
'delete the temporary queryDoCmd.DeleteObject acQuery, q
'set the variables back to nothingSet qd = Nothing
Set db = Nothing
'exit the function
'deletes the temporary query if it already existsIf Err.Number = 3012 And InStr(Err.Description, q) > 0 Then
DoCmd.DeleteObject acQuery, q
'displays a message box if QuickBooks is not openIf Err.Number = 3151 Then
MsgBox "QuickBooks is not open."
'prints the error information to the immediate windowDebug.Print Err.Number & " " & Err.Description
'Displays a message box with the error information for the user
MsgBox Err.Number & " " & Err.Description
Run the Function
To run the function, type:
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
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: