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.


Post a Comment

Popular Posts