QODBC QuickBooks Customer Table Access Tutorial

What This Code Does

Uses QODBC to import the Customer table from QuickBooks® and put it into a Microsoft® Access Table. This is a reference table that may be refreshed so do not alter or edit this table.



Uses for This Table

The customer table from QuickBooks®  may be linked to any number of tables in Microsoft®  Access to extend your customer information database.

For example, you may wish to:
  1. keep more information for customers in your database than QuickBooks®  accomodates. For example, personal information, birthdays, anniversaries, family members, hobbies, etc. to access when calling customers for a personal touch.
  2. keep sensitive customer information in a secure database
  3. link many records to one customer, for example, ABC Insurance Company may have three departments with seven people that you deal with but you do not wish to add each person to your QuickBooks®  file as a separate customer


Customers and Jobs

The customer table is really the jobs table for contractors or other QuickBooks® users who add jobs to customers. Every job will be listed in the customer table. To list only customers, use a query that limits Sublevels to 0 something like the query below. Instead of using the customer table, you would then use this SQL or a saved query with this SQL as the recordsource for your form or listbox.

SELECT qbCustomers.*
FROM qbCustomers
WHERE (((qbCustomers.Sublevel)=0))
ORDER BY qbCustomers.Name;



How to Link

ListID is a unique identifier for QuickBooks®  tables. Use each customer's ListID as a lookup field in your other database tables.




This Was Tested With

QODBC Desktop Read Write 2009 Version 9.00.00.253
QuickBooks®  Premier Contractor 2009
Microsoft®  Access 2002 SP3



How to Use This Function

Since this is a function, you can call it from any form. You could use a Refresh Customers button on a form to reload the customer table when new customers are added in QuickBooks or customer information changes. Since the ListIDs will always remain the same, the link will always work as long as you use the Customer table ListID as a lookup field in your database tables.

NOTE: If you decide to call this function from multiple forms, you will need a function that closes any OPEN forms using this table or Visual Basic will display an error message that the table cannot be deleted because it is in use.

If you only call this function from one form that uses this table as a recordsource (including controls), you must set any recordsources using this table to "" before calling the function and then set them back after the table is refreshed.

You may wish to place the Reload Customers button on a switchboard that does not use the table as a recordsource and then call a function that closes all open forms, reloads the customer table, and then opens the closed forms back up.



Microsoft Access References

You will need the following references checked from the Visual Basic Editor:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object





Code

Copy this code and paste it into a module in the Visual Basic Editor.

Function ReloadCustomers()
        '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 ReloadCustomers_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_Customers"
        '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       
        '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 * from Customer"
        'before you put the query records into a table, you may       
        'use a function to delete the table if it exists
90      Call fncDeleteTable("tblCustomers")
        'Turn off warnings so you don't get one for overwriting an existing table       
        'This table will be frequently overwritten 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 qbCustomers from " & qName
        'delete the pass thru query because you don't need it now
120     DoCmd.DeleteObject acQuery, qName
        'turn warnings abck 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
ReloadCustomers_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 - 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
ReloadCustomers_err:
Call fncWriteError("", "Functions Customers", "Function ReloadCustomers", 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 ReloadCustomers_exit
End Function


Additional Functions Required

You may already have these in your database if you have used other codes from VBQuick or The Generic Database. If you do not have them, you need to put them into a Module. The above code will not work without them. You need only copy and paste them. Each page provides a link to the code tutorial if you want to understand what they do and how they do it.


fncDeleteQuery:

fncDeleteTable:

fncWriteError:

QODBCConnect





Create the Customer Table

With your QuickBooks file open (if required) and all the code installed, simply type Reload Customers into the Immediate Window of the Visual Basic Editor and hit Enter like this:



Then move back to the Database Window to see the new table: qbCustomers.

Using a prefix to denote QuickBooks® tables helps avoid overwriting or deleting permanent tables that you create in Microsoft® Access. This code uses the prefix qb but you may change that in the code to anything you like. Remember to be consistent. Choose one prefix that you will use for tables imported from QuickBooks® that you will continually overwrite. You may use a prefix like temp, tmp, QODBC, etc.




Customers and Jobs or Just Customers?

Remember, this code queries the QuickBooks customer table using QODBC and puts it into a Microsoft Access table. The QuickBooks customer table contains customers and jobs. You may use the code as written and select only customer using a select query in your database as follows:

SELECT qbCustomers.*
FROM qbCustomers
WHERE (((qbCustomers.Sublevel)=0))
ORDER BY qbCustomers.Name;
However, you may also change the code only query the customers if you never plan to use jobs in your database. This will save time at the Pass Thru query level. To use QODBC to query only customers, replace line 80 with:

80      qDef.sql = "SELECT * FROM Customer where sublevel=0"



Asterisk

The asterisk includes all the customer fields in the query. To further speed up the QODBC query, you may select only the fields you require in your database.

To design a form that shows you the fields for any QuickBooks table available through QODBC, see this Three-Part Tutorial:

            QuickBooks Tables in Access w/ QODBC Form
            www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html

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:

No comments:

Post a Comment