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

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.

Example:

Parent                       Child
                      Invoice                      InvoiceLine
                      Estimate                     EstimateLine
                      CreditMemo                   CreditMemoLine

Select all 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.

Examples:
 

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

Select all 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.

Examples:
 

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

Select all 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
                      Import_err:
                      If InStr(1, err.Description, "Object 'qryTemp'", vbTextCompare) > 0 Then
                          DoCmd.DeleteObject acQuery, "qryTemp"
                          Resume
                      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
                      sQBFields_err:
                      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
                      sDBfields_err:
                      Debug.Print "Function sDBfields", Erl, err.Number, err.Description
                      End Function

Select all 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")

Select all 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.