Using QODBC to Consolidate Multiple QuickBooks Transaction Types for a One Form Fits All Result

Published on
8,031 Points
Last Modified:
Annaliese Dell
Find Annaliese on Amazon, Barnes & Noble and VBQuick.com.
This article explains how to consolidate QuickBoooks transaction types in a database so only one form or report can be used to view multiple transaction types. This article uses the tables:

     1.  InvoiceLine
     2.  EstimateLine
     3.  CreditMemoLine

The benefits include:

     1. smaller database
     2. less code
     3. less places to change code
     4. fewer forms and reports

To use this article, you need:
  1. Basic knowledge of SQL and VBA for applications
  2. QuickBooks
  3. Database application
  4. QODBC, an ODBC driver that lets you exchange information with QuickBooks using SQL. Yes, you can write to QuickBooks from your database using QODBC without learning the complicated QuickBooks SDK.
The first thing you need to know is how QuickBooks stores transactions. QuickBooks is a relational database.

Constant #1: Each of these transaction types has a parent table and a child table.

Parent                       Child
Invoice                      InvoiceLine
Estimate                     EstimateLine
CreditMemo                   CreditMemoLine

Open in new window

Constant #2: The unique identifier for these parent tables is TxnID.

Constant #3: The unique identifier for these child tables is [childtable]TxnLineID.

Invoice: TxnID                 InvoiceLine: InvoiceLineTxnLineID
Estimate: TxnID                EstimateLine: EstimateLineTxnLineID
CreditMemo: TxnID              CreditMemoLine: CreditMemoLineTxnLineID

Open in new window

I can see the smoke wafting out your ears from here as your brain jumps ahead to the possibilities this offers. Slow down so we can lay out how to apply this to your database.

It would take more than a short article to explain exporting so this article explains importing. With a basic understanding of QODBC and SQL, you can figure out the export side or wait for another article.

The first thing to decide is which set of field names to use in your database. To consolidate transaction types you need to use only one set of field names for all three types in the database. Then you can use only one form to service Invoices, Estimates and Credit Memos.

How is this possible? Because the field names for each of these transaction types differs only in the prefix of certain fields. I realize this gets fuzzy, especially for people unfamiliar with QODBC and QuickBooks so a visual is in order here.

Notice the similarity of field names. Three more constants:

Constant #4: Child tables contain all the information in the parent table.

Constant #5: All field names are the same for the child table fields (excluding the prefix) though not all tables contain the same NUMBER of fields.

Constant #6: Child table field names [not in the parent table] consist of the NAME OF THE CHILD TABLE + THE FIELD NAME.

InvoiceLine                     EstimateLine                     CreditMemoLine
-----------                     ------------                     --------------
TxnID                           TxnID                            TxnID
TxnDate                         TxnDate                          TxnDate
RefNumber                       RefNumber                        RefNumber
InvoiceLineTxnLineID            EstimateLineTxnLineID            CreditMemoLineTxnLineID
InvoiceLineMemo                 EstimateLineMemo                 CreditMemoLineMemo
InvoiceLineRate                 EstimateLineRate                 CreditMemoLineRate

Open in new window

To see all available field names for a specific table, type sp_columns tablename into the VB Demo that comes with QODBC. Replace tablename with the table of your choice.

          Example:  sp_columns invoiceline

To see all available tables, type:  sp_tables

This article uses the InvoiceLine  table as an example.

Next, decide what fields to use in your database. You probably don't need all 159 fields. Limiting the pass-through query to only the fields you need speeds up the import.

To create the field tables:

1. Create a pass-thru query named “qryTemp”
                a.  connection string="ODBC;DSN=QuickBooks Data;SERVER=QODBC"
                b. sql: sp_columns InvoiceLine

2. Create a make-table query, SQL: "select COLUMNNAME into Fields_InvoiceLine from qryTemp".

3. Run the make-table query.

4. Open the table and delete the fields you don't want to use in the database

5. Repeat steps 1 thru 4 for the EstimateLine and CreditMemoLine tables.

Important! Keep only the common fields in all three tables. You can get around this with a bit of extra code but we're keeping it simple.

Do not move any of the fields. Some procedures between QuickBooks and QODBC require that field names appear in the same order in the SQL statement as they do in QuickBooks. This doesn't matter for import but if you ever plan to export, you’ll be set up for it.

Since we're only dealing with import and all fields are 'queryable', the process is straight forward. Sample code is provided but the steps are:

1. Write a function that creates a string for the import fields by iterating through the fields table of the corresponding QuickBooks table to import. Example: to import EstimateLines, iterate through the Fields_EstimateLine table you created earlier.

2. Create the SQL string using passed-thru variables:

                a. database table name to store the imported data
                b. QuickBooks table to import
                c. beginning date
                d. ending date

3. Create a pass-thru query using the SQL string

4. You cannot create tables from the pass-thru query so create a Make-Table query to import the data into the database table.

5. Turn the imported field names into InvoiceLine[something]. How? Write a function that iterates through the Fields_[QuickBooks table] and adds " as InvoiceLine[something]". Example:

          "EstimateLineItemRefFullName as InvoiceLineItemRefFullName"

6. Add []" & QBTableName & " as QBTableName] to the SQL string to denote what kind of transaction the finished table contains. This adds the field: QBTableName.

7. Create a Make-Table query using the strings in steps 5 and 6.

See this sample code. In the interest of brevity, extra error trapping, variables, setwarnings, etc. are omitted.
Sub Import(sTableName As String, QBTableName As String, dtFrom As Date, dtTo As Date)
On Error GoTo Import_err

'*** create sql
100     Dim sSQL As String
110     sSQL = "select " & sQBFields(QBTableName) & " from  " & QBTableName & " where txndate>=" & fQBDate(dtFrom) & " and txndate<=" & fQBDate(dtTo)

'*** create pass-thru query
200     Dim db As DAO.Database, qd As DAO.QueryDef
210     Set db = CurrentDb
220     Set qd = db.CreateQueryDef("qryTemp")
230     qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
240     qd.ReturnsRecords = True
250     qd.ODBCTimeout = 60
260     qd.SQL = sSQL
270     Set qd = Nothing
280     Set db = Nothing

'*** import into table
300     DoCmd.RunSQL "select '" & QBTableName & "' as QBTableName ," & sDBfields(QBTableName) & " into " & sTableName & " from qryTemp"

Exit Sub
If InStr(1, err.Description, "Object 'qryTemp'", vbTextCompare) > 0 Then
    DoCmd.DeleteObject acQuery, "qryTemp"
End If
Debug.Print "Sub Import", Erl, err.Number, err.Description
End Sub
Function fQBDate(myDate As Date) As String
fQBDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function

Function sQBFields(QBTableName As String) As String
On Error GoTo sQBFields_err
'*** open the Fields table for the QBTable you're importing as a recordset
100     Dim db As DAO.Database, RS As DAO.Recordset, i As Integer
110     Set db = CurrentDb
120     Set RS = db.OpenRecordset("SELECT COLUMNNAME FROM FIELDS_" & QBTableName)
130     If RS.RecordCount > 0 Then
140         RS.MoveLast
150         RS.MoveFirst
160         Do While Not RS.EOF
170             sQBFields = sQBFields & RS!COLUMNNAME & ","
180         RS.MoveNext
190         Loop
200     End If
210     RS.Close
220     Set RS = Nothing
230     Set db = Nothing
240     If Nz(sQBFields, "") <> "" Then sQBFields = Left(sQBFields, Len(sQBFields) - 1)

Exit Function
Debug.Print "Function sQBFields", Erl, err.Number, err.Description
End Function
Function sDBfields(QBTableName As String) As String
On Error GoTo sDBfields_err
'*** open the Fields table for the QBTable you imported
100     Dim db As DAO.Database, RS As DAO.Recordset, i As Integer
110     Set db = CurrentDb
120     Set RS = db.OpenRecordset("SELECT COLUMNNAME FROM FIELDS_" & QBTableName)
130     If RS.RecordCount > 0 Then
140         RS.MoveLast
150         RS.MoveFirst
160         Do While Not RS.EOF
170             sDBfields = sDBfields & RS!COLUMNNAME
                    '*** if the imported table is NOT InvoiceLine, replace the pertinent field names with "InvoiceLine"
                    '*** for example, if you imported the CreditMemoLine table, replace all the fields containing the
                    '*** string "CreditMemo" (QBTableName) with "InvoiceLine"
180                 If QBTableName <> "InvoiceLine" And InStr(1, RS!COLUMNNAME, "line", vbTextCompare) > 0 Then
190                     sDBfields = sDBfields & " as " & Replace(RS!COLUMNNAME, QBTableName, "InvoiceLine")
200                 End If
210             sDBfields = sDBfields & ","
220             RS.MoveNext
230         Loop
240     End If
250     RS.Close
260     Set RS = Nothing
270     Set db = Nothing
280     If Nz(sDBfields, "") <> "" Then sDBfields = Left(sDBfields, Len(sDBfields) - 1)

Exit Function
Debug.Print "Function sDBfields", Erl, err.Number, err.Description
End Function

Open in new window

Now you only need one form or report to for all three types: EstimateLines, InvoiceLines and CreditMemoLines. You can also import all three types into separate tables and put them into one big table using a union query. Each transaction will denote what type it is in the field: QBTableName.

This is a principle you can apply to the parent tables as well. You can pass additional variables, required or optional, such as CustomerRefFullName, RefNumber, TxnID, etc. to further limit results. You can include field names that are not common to all three tables with a few extra functions/procedures if you like.

Editing information in QuickBooks is almost as simple, a reversing of the import field strings. Example: to export EsimateLines, create a string of field names using the Fields_InvoiceLine table but replace “Invoice” with “Estimate”. Use the SQL rules for QODBC of  course.

Don't forget to turn dates into the QODBC format as shown in the function: fQBDate

Inserting new information is a bit more complicated since inserting an Invoice, Estimate or CreditMemo, requires inserting via the child table, saving to cache, etc. There are two ways to do this and you can find additional information via an internet search for 'QODBC insert invoice' or similiar phrase.

To test the code, type something like this into the immediate window of the Visual Basic editor.
Call import("tblTemp","creditmemoline","1/1/15","2/2/15")

Open in new window

Once you understand the principle, you'll want to use it for other transaction types and create procedures that export from a single form as well.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free