Viewing Past Transactions Without Opening QuickBooks
Published on
15,075 Points
Annaliese Dell
Find QODBC and tech tips from Annaliese on Amazon, Barnes & Noble and twitter.com/vbquick
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
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.
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.
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
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.
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
First, create a new database and select the following references:
Create a new module and copy and paste the following code into it.
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.
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.
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
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.
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:
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
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:
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
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.
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.