Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Viewing Past Transactions Without Opening QuickBooks

Annaliese DellSec-Treas
QODBC and tech tips
Published:
Updated:
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:

import and store tables by date and then combine them for quick access, only refreshing specific date tables when necessary.


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:

References (click to enlarge)
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

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:

MakeTableQBTransactions

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:

Form (click to enlarge)
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

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.
3
5,896 Views
Annaliese DellSec-Treas
QODBC and tech tips

Comments (1)

CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
Another very nicely done article.
"Yes" vote above.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.