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
- QODBC read or write version (or free trial version)
- Microsoft® Access
- QODBC Desktop Read Write 2009 Version 9.00.00.253
- QuickBooks® Premier Contractor 2009
- 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 customerThis 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.
QuickBooks® also assigns each account a unique identifier known as ListIDs. These make excellent Microsoft® Access unique identifiers because:
- QuickBooks® assigns them
- they are unique
- 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
How to Use this Procedure
Create a new module and select these references:
- Visual Basic for Applications
- Microsoft® Access 10 Object Library
- Microsoft® DAO Object Library
The Generic Database:
Chapter 01: Part 2: How to Create a New Module and Select References
- Copy and paste the code below into the Visual Basic editor of the new module you created.
- Type: GetQuickBooksTable into the Immediate Window.
- Supply the name of the table you want. QuickBooks® contains over 100 tables. Some examples:
- Allow ample time for QODBC to retrieve records from QuickBooks®
- You may need to close the Visual Basic Editor to view the query results in Microsoft® Access.
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.
VBA Access Function fncDeleteQuery Code Only
VBA Access Function fncQODBCConnect Code Only
'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
'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
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:
- close the query
- create a new table from the results
To view the new table:
- Close the Visual Basic Editor.
- Select Tables from the Objects list of the database window
- Look for tables beginning with the prefix: tbl_
- Double-click the table to open and view the results.
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: