Linking Spreadsheets in Microsoft Access

This is part of a tutorial on viewing QuickBooks transactions while QuickBooks is closed. The tutorial begins here.

Tutorial: Part 2

The method in Part 1 used spreadsheets to store and access QuickBooks data. QuickBooks makes exporting spreadsheets fast and painless; however, accessing, filtering and searching spreadsheets leaves room for improvement. We can improve on this method by using a database.

A database lets you combine tables to search all data at once. Remember our three tables?

A database can combine these tables through VBA or queries into one big table for fast, easy searches.

Since spreadsheet data changes, we need a quick way to update it in the database and that solution is Linking. Linking lets you export spreadsheets from QuickBooks when it's open and refresh the spreadsheets when it's closed.

Any database will do but this tutorial uses Microsoft Access in examples.

These steps were recorded using Microsoft Access 2002. Your version of Microsoft Access may differ in appearance but the basic steps remains the same.

Only refresh the linked tables when you need the data. In other words, when you change transactions in QuickBooks, export the memorized spreadsheet but do not open the database and refresh the link at that time. Close QuickBooks and only refresh the links when you open the database to view current information.

For example, perhaps on Monday you needed to change a memo or make a note in a 2011 transaction. You open the 2011 QuickBooks file and make the change. Before closing QuickBooks, export the memorized spreadsheet and do nothing else.

Then on Friday, you need to see something in the 2011 QuickBooks transaction while you are working in the 2012 QuickBooks file. You open the database, refresh the linked table and view the most current transaction data.

Maybe you are an accountant or bookkeeper working in the ABC QuickBooks file. Before closing QuickBooks, export the spreadsheet and do nothing else. Later in the week, you are working in the XYZ file when the ABC client calls with a question. Open the database, refresh the linked table and view the current data without closing XYZ QuickBooks. Hang up and continue working without losing where you were.

We can improve on the manual linking method by writing a bit of code and creating a button on a form to refresh the linked tables and this is explained in the next post.

Continue to the next post or keep checking back if it is not written yet.

How to View QuickBooks Transactions with QuickBooks Closed

Tutorial: Part 1

Do you:
  • have more than one QuickBooks file?
  • archive QuickBooks files occasionally?
  • ever start a new company file to speed performance?
  • store client files?
  • frequently close one file and open another for yourself or clients?

The solution is:

Store QuickBooks transactions in date chunks in another application so you can access them when QuickBooks is closed.

In addition to transactions, this solution also works for accounts, vendors, customers, items, etc. Therefore, we will refer to this information as:
  • source data: residing in QuickBooks files
  • stored data: exported from QuickBooks and stored in another application

How the Solution Works for Historical Data

Since historical data rarely changes, you can rely on stored data as long as you refresh it when you change the source data.

For example, you may store transactions in blocks by year and have three tables:
  1. 2010
  2. 2011
  3. 2012
You may frequently access these tables with QuickBooks closed. To change the data, however, you must open QuickBooks. After changing the data (transactions, account names, items, etc) and before closing QuickBooks, refresh the table so it remains accurate until the next time you open QuickBooks.

To review:

The only data you need to refresh is data you change in the open QuickBooks file.
You cannot change stored data while QuickBooks is closed, therefore it remains accurate until you open QuickBooks and change it. The only exception is when using ODB connections and certain file permissions but even then you can only change one file and all others must be opened to change data.
After changing data and before closing QuickBooks, refresh the stored data.

Multiple Company Files

The same principle applies to multiple company files. You cannot change any company's QuickBooks data unless the file is open. Before closing the file, refresh the stored data and it remains accurate when QuickBooks is closed.

Store multiple company files by date but also include a higher level of identifier: Company Name. Your list of stored tables might look something like this:

ABC Company 2009
ABC Company 2010
ABC Company 2011
XYZ Company 2009
XYZ Company 2010
XYZ Company 2011

Accessing Paperless Receipts

Another benefit of stored data is access to paperless receipts with QuickBooks closed. If you use the The Link Principle (Amazon, Barnes & Noble) to link transactions to paperless receipts, you can view any receipt in seconds from the data in your stored transaction list.

How to Store QuickBooks Transactions

A variety of methods exist and we'll begin with the easiest but least effective and end with the most effective that uses VBA. Copy and paste VBA is included so you need not know VBA to use the method.

Method #1:

The simplest way to store QuickBooks transactions is to export to spreadsheets. QuickBooks provides this feature for you. To include all transactions, use the Custom Transaction Detail report, unless you only want specific transactions like checks, bills or credit cards or variations of combinations of these.

To create the Custom Transacction Detail report, follow these steps (see video below):

  1. click Reports
  2. select Custom Reports
  3. select Transaction Detail
  4. select a Date Range
  5. select the Columns you want on your report
  6. click OK
  7. click Memorize
  8. name the Report so you can find it later
  9. save it in a Report Group or create a new Report Group
  10. click OK
  11. click Excel
  12. create a New Worksheet the first time; thereafter, if you are saving the same date range,
    select Update Existing Worksheet
  13. save the report
  14. if updating an existing worksheet, select the sheet from the drop-down box QuickBooks provides for you that lists all worksheets in the select workbook

How and where you store spreadsheets is up to you. You might store each company in a separate workbook and keep each date block (year, quarter, etc.) on a separate worksheet. The possibilities are many.

This is the easiest method as far as exporting; however, saving and accessing spreadsheets means remembering their locations. In addition, each date block will reside on at least its own worksheet if not its own workbook. You may need to open more than one workbook or worksheet to find the transaction you need. This is still faster than opening QuickBooks each time you need to view a transaction, especially if you have to close one file and open another.

We can improve on this method by using a database and this is covered in the next post.

Continue to the next post

Microsoft Access Database Error -2147352567: The data has been changed.

If you receive this message and you know no one else has changed your record, does your form contain unbound controls?

If you tried DoCmd.RunCommand acCmdSaveRecord to no avail, try requerying the form or subform.

If a subform triggers the error and requerying the subform doesn't work, try Me.Parent.Requery to requery the subform's parent form.

Pleasant Surprise! QODBC and QuickBooks 2012 Offer More Updateable Fields

If you have been to this site before, you probably know I use QODBC to write data from Microsoft Access to QuickBooks and to import data from QuickBooks into Microsoft Access.

I recently upgraded to QuickBooks 2012 from QuickBooks 2009 which also meant upgrading QODBC. I wish I'd upgraded sooner. What a pleasant surprise to discover previously non-updateable fields are now updateable.

For example, in the older versions of QuickBooks and QODBC, CheckExpenseLine memos were not updateable which meant moving back to QuickBooks from Microsoft Access to add or change wording in these memos. These are the memos on the check lines and not the checks.

DepositLineMemos are also updateable now.

If you use The Link Principle to link transactions to receipts and use QODBC, this is very good news because you can now automate your database to create the links for these previously non-updateable fields and write them to QuickBooks from your database.

If you are still composing memos like "ink for printer" or "fixed brakes on truck" and you scan receipts into the computer, you are missing a golden opportunity. For more on how to organize paperless records in seconds plus link them to QuickBooks, Quicken or other financial software, see The Link Principle (Amazon or Barnes & Noble).