How to Place Hidden Quickbooks Table Fields into Microsoft Access

What Lies Beneath

Did you ever wonder what lies beneath your QuickBooks® file? The columns and rows of QuickBooks® tables may surprise you. QODBC makes viewing this hidden information a snap.

Even if you never plan to exchange information between QuickBooks® and Microsoft® Access using QODBC, seeing what lies beneath gives you a better understanding of your data file.

Once you see how quickly QODBC works and how easy it is to use, you may decide to step outside your comfort zone a wee bit and write your own QuickBooks® add-ons.

This post provides quick results with minimum effort on your part. Are you ready? Let's go!



What You Need

  1. QuickBooks®
  2. QODBC read or write version (or free trial version)
  3. Microsoft® Access
This code was tested with:


  1. QODBC Desktop Read Write 2009 Version 9.00.00.253
  2. QuickBooks® Premier Contractor 2009
  3. Microsoft® Access 2002 SP3

Purpose of Code


You do not need to know the purpose of this code to benefit from it. You may prefer to get right to the good stuff and play with QODBC. For the diligant and curious:

The code below retrieves QuickBooks® field names for given tables. You need field names to create SQL strings to extract data from QuickBooks®.

You type the SQL string into the SQL Design View window in Microsoft® Access or in your code. Do not worry if you do not understand this or know how to do it. Just keep reading and you will get there.



Talking to QuickBooks®

SQL stands for Structured Query Language. This is a fancy way of saying that you must speak to computers in a specific way so they understand what you want. Naturally, you cannot type: get all my checks from QuickBooks® into your query. You must format questions correctly. For example:

Select * from customer
This is a simple query that asks Microsoft® Access to retrieve all the data from the QuickBooks® customer table. You absolutey may use this query. Simply replace customer with other table names. You need never become any more complicated than the above query. However, all the data means names, addresses, phone numbers including data you most likely do not care about.




Using Fields Makes Queries Run Faster

Retrieving fields you do not want wastes time, especially with a long customer list. Knowing what fields you want lets you limit results and save time.

For example, perhaps you need a list of customers for use in your Microsoft® Access database. The field names QuickBooks® uses internally often differ from the field names displayed to you. For example, Name, FullName, CompanyName and ParentRefFullName are all customer fields. You need to know which field you want.



Unique Identifiers

QuickBooks® also assigns each account a unique identifier known as ListIDs. These make excellent Microsoft® Access unique identifiers because:
  1. QuickBooks® assigns them
  2. they are unique
  3. no one can change them

If you plan to link database records to QuickBooks® records, you need to know the field names for the ListIDs in each table. The customer table, for example, contains: ListID,  ParentRefListID, CustomerTypeRefListID, TermsRefListID, etc. Do not dismay at the odd sounding field names. After you play with the tables for a while, you will notice a logical pattern to their construction and no longer need to look them up.

For an example of the difference between the fields available from the QuickBooks® application and the fields available when tables are accessed using QODBC see:

      Import QuickBooks Tables: IIF vs. QODBC
      http://www.vbquick.com/2011/10/import-quickbooks-table-iif-or-qodbc.html



How to Use this Procedure



Create a new module and select these references:

  1. Visual Basic for Applications
  2. Microsoft® Access 10 Object Library
  3. Microsoft® DAO Object Library



If you do not know how to do this, 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




  1. Copy and paste the code below into the Visual Basic editor of the new module you created.
  2. Type: GetQuickBooksTable into the Immediate Window.
  3. Supply the name of the table you want. QuickBooks® contains over 100 tables. Some examples:
    1. Account
    2. Bill
    3. Check
    4. Class
    5. Company
    6. CreditCardCharge
    7. CreditCardCredit
    8. CreditMemo
    9. Customer
    10. Deposit
    11. Employee
    12. Entity
    13. Estimate
    14. Invoice
    15. Item
    16. JournalEntry
    17. PriceLevel
    18. PurchaseOrder
    19. SalesOrder
    20. SalesReceipt
    21. TimeTracking
    22. Vendor
  4. Allow ample time for QODBC to retrieve records from QuickBooks®
  5. You may need to close the Visual Basic Editor to view the query results in Microsoft® Access.


Additional Codes

If this is your first time using code from VBQuick, you need two functions. These functions let me shorten codes for you by performing simple tasks like deleting temporary tables and queries and creating the QODBC connection string. You should place them into one module so you can import them into new databases for use with other VBQuick codes.

      fncDeleteQuery:
            VBA Access Function fncDeleteQuery Code Only
            www.vbquick.com/2011/10/microsoft-access-delete-query-function.html     

     QODBCConnect:

           VBA Access Function fncQODBCConnect Code Only
            www.vbquick.com/2011/10/vba-microsoft-access-function-qodbc.html



Code

Sub GetQuickBooksTable()   
'Declare variables for database, querydef, query, and table
Dim db As DAO.Database, qDef As DAO.QueryDef, qName As String, strTable As String
'ask the user for the name of the table by using an input box
'or pass the name of the table to strTable as an argument like this:
'Sub GetQuickBooksTable(strTable as string)
'user input below
strTable = InputBox("Enter table name:", "Table Name", "type here") 
'give the query a name
qName = "temp"  
'function deletes query if it already exists
Call fncDeleteQuery(qName)    
'set the database to work in as the current database
Set db = CurrentDb
'create the QueryDef and give the name you decided earlier: "temp"
Set qDef = db.CreateQueryDef(qName)
'use this function to set the connection string
qDef.Connect = QODBCConnect
'set the query to one that return records   
qDef.ReturnsRecords = True
'place an sql string in the query
qDef.SQL = "sp_columns " & strTable    
'now you have a query that will retrieve the QuickBooks table
'you can put that information into a table or use it in a report or another query
'you are finished with qDef and db so set them to nothing   
Set qDef = Nothing
Set db = Nothing
'open the query to view the results if desired   
DoCmd.OpenQuery qName
End Sub



Creating a Table

You may create a table from the results. Add this code immediately before End Sub:
DoCmd.Close acQuery, qName

DoCmd.RunSQL "select * into tbl" & strTable & " from " & qName

The above lines:
  1. close the query
  2. create a new table from the results
The name of the table will contain the prefix: tbl. For example, if you import the customer table, the new table's name will be: tbl_customer.

To view the new table:

  1. Close the Visual Basic Editor.
  2. Select Tables from the Objects list of the database window
  3. Look for tables beginning with the prefix: tbl_
  4. Double-click the table to open and view the results.
Enjoy making your tables and please let me know how this worked for you.

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