Viewing Past Transactions Without Opening QuickBooks

You may need to view past transactions from previous QuickBooks files or other QuickBooks company files when:

      1. you are working in another QuickBooks file
      2. QuickBooks is closed

QuickBooks users who would benefit from this article are:

      1. Accountants or bookkeepers with multiple company files
      2. Companies with archived QuickBooks files for one company
      3. The Link Principle users who need to open receipts without opening QuickBooks

Using Microsoft Access

One way to import QuickBooks transactions into Microsoft Accesss is with spreadsheets. This is time consuming since you must first locate and open a memorized report and then save the spreadsheet exactly where you saved it last time.

A faster method exists for transferring data to Microsoft Access that does not require spreadhseets or QuickBooks reports. This article explains how to use VBA (Visual Basic for Applications) to exchange information with QuickBooks with an ODBC utility called QODBC. Copy and paste code is provided so you do not even have to know VBA to apply this method.

If you already know VBA, you can alter the code to suit your needs.

QODBC lets Microsoft Access read, insert and edit QuickBooks data directly without the need to learn the complicated QuickBooks SDK. Therefore, those with limited VBA knowledge can still create and customize their own QuickBooks add-ons.

Even if you do not use QODBC or have no intention of using QODBC, you may still use the principle in this article with other import methods, even QuickBooks SDK, to store QuickBooks transactions from multiple files in one location.

A free trial version of QODBC (available at the time of this writing) can be used with the code in this article for quick results to see if this method fills your needs.

Premise

The premise of this method is that once a transaction is stored in QuickBooks, it does not usually change. Even if you change recently stored transactions, you most likely do not change historical transactions. Yet when historical transactions reside in other QuickBooks files, you can only access them by closing the current QuickBooks file and opening the other file.

The method here queries blocks of QuickBooks transactions by date. Since past transactions rarely change, you rarely need to refresh the tables. If you change past transactions, you are working in the past transaction QuickBooks file and that is the perfect time to refresh the database table before closing the QuickBooks file.

The code uses a union query to combine the transaction tables and store them in one big table. Then you have access to past transactions as far back as you wish limited only by the Microsoft Access table size limit.

For example, you may wish to store transactions by year, quarter or even month depending on how many transactions your QuickBooks files normally hold. Typically, the only files you would need to refresh would be the most recent, perhaps only the most recent quarter or year.

If you work with several company files, the code first queries QuickBooks for the name of the company open at the moment and incorporates that company name into combined table name. You can then create a form with a listbox or combobox filled with company names. Program the On_Click event of the control to set the form's record source to the selected company table.

Requirements

1.) QuickBooks
2.) QODBC
3.) Microsoft Access
4.) Three Microsoft Access Library References
5.) the same company name must be used for all QuickBooks files for that company
6.) all company files must be converted to the current QuickBooks version

Premise

The code below uses a principle that can be applied to other programming languages. The principle is:



For example, you may import and store transaction tables dating from 1999 to 2012 by year. The only table you would typically need to refresh would be the 2012 table, since past transactions rarely change other than perhaps a memo or reference number or other insignificant detail. When you change past transaction details and have that QuickBooks file open, you may quickly refresh the table  before closing QuickBooks.

Process

The code process is:

1) query the QuickBooks file for the name of the company
2) use the company name in the table names (in case you store multiple companies)
3) request user input or pass variables for beginning and ending dates
4) create a pass-through query using the CustomTxnDetail report
5) select the pass-through query results into a table incorporating the dates into the table name
6) create a union query combining tables from the same company and order by date ascending
7) select results from the union query into one combined table using the company name

Steps

First, create a new database and select the following references:


Create a new module and copy and paste the following code into it.

Sub MakeTableQBTransactions()
On Error GoTo MakeTableQBTransactions_err
Screen.MousePointer = 11
Dim Response, s As String
s = "Continuing will create a new table of vehicle " & Chr(10) & _
"transactions from the QuickBooks file currently open." & Chr(10) & Chr(10) & _
"This will not affect transactions in the database " & Chr(10) & _
"that were imported from other QuickBooks files." & Chr(10) & Chr(10) & _
"Depending on the number of transactions in the open" & Chr(10) & _
"QuickBooks file, this may take several minutes." & Chr(10) & Chr(10) & _
"Do you wish to continue?"

Response = MsgBox(s, vbCritical + vbYesNo, "Continue?")
If Response = vbNo Then Exit Sub

Dim dtFrom As Date, sFrom As String, dtTo As Date, sTo As String

dtFrom = InputBox("Enter the beginning date you wish to import:", "Beginning Date", "##/##/##")
dtTo = InputBox("Enter the ending date you wish to import:", "Ending Date", "##/##/##")

sFrom = "{d '" & Year(dtFrom) & "-" & Right("00" & Month(dtFrom), 2) & "-" & Right("00" & Day(dtFrom), 2) & "'}"
sTo = "{d '" & Year(dtTo) & "-" & Right("00" & Month(dtTo), 2) & "-" & Right("00" & Day(dtTo), 2) & "'}"

Dim db As DAO.Database, qd As DAO.QueryDef, t As String, q As String, QBCompany
QBCompany = fncQBCompany
q = "tempQuery"
t = "tblQBTrans_" & QBCompany & " " & _
Year(dtFrom) & Right("00" & Month(dtFrom), 2) & Right("00" & Day(dtFrom), 2) & " to " & _
Year(dtTo) & Right("00" & Month(dtTo), 2) & Right("00" & Day(dtTo), 2)
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.ReturnsRecords = True
qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

qd.ODBCTimeout = 60
s = "sp_report CustomTxnDetail show TxnType, Date, RefNumber," & _
" Name, SourceName, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit " & _
" parameters datefrom =" & sFrom & " , dateto=" & sTo & "," & _
" SummarizeRowsBy = 'TotalOnly' "
    qd.SQL = s
    DoCmd.SetWarnings False

    DoCmd.RunSQL "select * into [" & t & "] from [" & q & "]"

Set qd = Nothing

Set qd = db.CreateQueryDef(q)

s = ""
Dim x As Integer
For x = 0 To db.TableDefs.Count - 1
    If InStr(1, db.TableDefs(x).Name, "tblQBTrans_", vbTextCompare) > 0 Then
    s = s & "select * from [" & db.TableDefs(x).Name & "] union "
    End If
    
Next x
s = Left(s, (Len(s)) - 7) & " order by date;"
qd.SQL = s
DoCmd.RunSQL "select * into [Transactions " & QBCompany & "] from [" & q & "] where txntype is not null"



Set qd = Nothing
Set db = Nothing
DoCmd.OpenTable "Transactions " & QBCompany
Screen.MousePointer = 0
Exit Sub
MakeTableQBTransactions_err:
If Err.Number = 3012 Then
    DoCmd.DeleteObject acQuery, q
    Resume
End If
Debug.Print Erl & ": " & Err.Number & " " & Err.Description
End Sub
Function fncQBCompany()
On Error GoTo fncQBCompany_err
Dim db As DAO.Database, qd As DAO.QueryDef, rs As DAO.Recordset, q As String, t As String
q = "qCompanyName"
t = "tblCompanyName"
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

qd.ReturnsRecords = True
qd.ODBCTimeout = 60
qd.SQL = "select companyname from company"
DoCmd.SetWarnings False
DoCmd.RunSQL "select * into " & t & " from " & q
Set rs = db.OpenRecordset(t)
rs.MoveLast
rs.MoveFirst
fncQBCompany = rs!companyname
fncQBCompany = Replace(Replace(fncQBCompany, ",", ""), ".", "")
rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
Exit Function
fncQBCompany_err:
If Err.Number = 3012 Then
    DoCmd.DeleteObject acQuery, q
    Resume
End If
Debug.Print Erl & ": " & Err.Number & " " & Err.Description

End Function

Select all Open in new window

Run the Code

With QuickBooks open and QODBC installed on your computer, type this into the Immediate Window of the Visual Basic Editor and press Enter:


Wait until the cursor no longer indicates busy and the table opens. The ODBC timeout is set to 60. If you are attempting to import many transaction, you may need to increase this or select a shorter time period. When the hour glass no longer indicates busy, the newly imported table should open for you.

Additional Ideas for Creating a Form

You may desire a form to view transactions and call the import procedure. Some ideas for your form are:

1) user input boxes for datefrom and dateto
2) listbox of company tables to choose from
3) button to run the procedure
4) using The Link Principle (Amazon, Barnes & Noble) and VBA, provide a button on each transaction line to open its corresponding paperless receipt
6) text boxes to filter form for vendor, account, amount, date, etc.

You may design your form to look something like this:


Alter the Procedure that imports the transactions by passing the date variables as in this code below. The function that imports the company name stays the same.

Sub ImportTransactions(dtFrom As Date, dtTo As Date, frm As Form)
On Error GoTo ImportTransactions_err
Screen.MousePointer = 11
frm.RecordSource = ""
Dim Response, s As String
s = "Continuing will create a new table of vehicle " & Chr(10) & _
"transactions from the QuickBooks file currently open." & Chr(10) & Chr(10) & _
"This will not affect transactions in the database " & Chr(10) & _
"that were imported from other QuickBooks files." & Chr(10) & Chr(10) & _
"Depending on the number of transactions in the open" & Chr(10) & _
"QuickBooks file, this may take several minutes." & Chr(10) & Chr(10) & _
"Do you wish to continue?"

Response = MsgBox(s, vbCritical + vbYesNo, "Continue?")
If Response = vbNo Then Exit Sub

Dim sFrom As String, sTo As String

sFrom = "{d '" & Year(dtFrom) & "-" & Right("00" & Month(dtFrom), 2) & "-" & Right("00" & Day(dtFrom), 2) & "'}"
sTo = "{d '" & Year(dtTo) & "-" & Right("00" & Month(dtTo), 2) & "-" & Right("00" & Day(dtTo), 2) & "'}"

Dim db As DAO.Database, qd As DAO.QueryDef, t As String, q As String, QBCompany
QBCompany = fncQBCompany
q = "tempQuery"
t = "tblQBTrans_" & QBCompany & " " & _
Year(dtFrom) & Right("00" & Month(dtFrom), 2) & Right("00" & Day(dtFrom), 2) & " to " & _
Year(dtTo) & Right("00" & Month(dtTo), 2) & Right("00" & Day(dtTo), 2)
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.ReturnsRecords = True
qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

qd.ODBCTimeout = 60
s = "sp_report CustomTxnDetail show TxnType, Date, RefNumber," & _
" Name, SourceName, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit " & _
" parameters datefrom =" & sFrom & " , dateto=" & sTo & "," & _
" SummarizeRowsBy = 'TotalOnly'"
    qd.SQL = s
    DoCmd.SetWarnings False

    DoCmd.RunSQL "select * into [" & t & "] from [" & q & "]"

Set qd = Nothing

Set qd = db.CreateQueryDef(q)

s = ""
Dim x As Integer
For x = 0 To db.TableDefs.Count - 1
    If InStr(1, db.TableDefs(x).Name, "tblQBTrans_", vbTextCompare) > 0 Then
    s = s & "select * from [" & db.TableDefs(x).Name & "] union "
    End If
    
Next x
s = Left(s, (Len(s)) - 7) & " order by date;"
qd.SQL = s
DoCmd.RunSQL "select * into [Transactions " & QBCompany & "] from [" & q & "] where txntype is not null"



Set qd = Nothing
Set db = Nothing
Screen.MousePointer = 0
frm.RecordSource = QBCompany
Exit Sub
ImportTransactions_err:
If Err.Number = 3012 Then
    DoCmd.DeleteObject acQuery, q
    Resume
End If
Debug.Print Erl & ": " & Err.Number & " " & Err.Description
End Sub

Select all Open in new window

To use the code above, be sure to create the two text boxes on your form for user input. You must name them as specified below because the code uses these control names to pass to the procedure variables:

      dtFrom
      dtTo

Summary

Many reasons exist for maintaining multiple QuickBooks files such as multiple companies or starting a new QuickBooks file due to slow performance.

Since only one QuickBooks file can be opened at a time, storing all QuickBooks transactions in one database lets you access transactions without changing QuickBooks files or even opening the QuickBooks application.

Past transactions rarely change so opening QuickBooks to view them is not usually necessary. If you do change a transaction, you can refresh the database file before closing QuickBooks.

If you use The Link Principle to save paperless receipts, you can program your database form to open paperless receipts for past transactions while QuickBooks is closed or while working in another QuickBooks company file.