Leveraging Microsoft Access® and Microsoft Excel®

© by Mike Robinson

Well, no one at Microsoft really said that to me, but sometimes I do wonder. Why else would the single most-powerful component of the Microsoft Office® suite be so-completely downplayed? You can get Microsoft Access® in most-but-not-all of the Office distributions, yet when you do a “standard install,” Access may or may not be included by default. Anyhow, it just sits there, largely-ignored and unexplored, while the workplace “Excel® gurus” (and they are gurus...) pound away at their enormous spreadsheets. They're gurus, and they work miracles, but they might be doing it the hard way.

Microsoft makes a substantial amount of their annual revenue from Office, and like most companies they fully understand that people really want to learn as little about a product as they must. People might choose to learn more, as time permits, but they really do need to be able to spend as little time as possible studying the thing. Once they devise some strategy that ought to apply to what they want to do, they want to be able to do it right away and they want it to more-or-less work. Nothing wrong with that, mind you:   that's exactly the way I feel about my car.

Even though they don't talk about it too much (at least outside of the Microsoft Developer Network™ website), there's a lot more to the Office suite than meets the eye. And I think I can explain it to you well enough without any escapades into “outer geekdom.”

In fact, it all makes perfect sense ... it borders on brilliant ... as long as you think outside the boxes that say “Excel” and “Access.”

A water-color painting

So, come with me. Let me take this familiar water-color painting we call Microsoft Office and spray a little mist of water on it. Now let the sharp lines between “individual applications” start to blur together ... so that we begin to see the whole thing not as a collection of discrete applications, but rather a collection of objects. Of “things.” A spreadsheet, or a page in the spreadsheet, or even an individual cell. Microsoft Excel or Microsoft Access itself. The various pieces, subsystems, and capabilities of each of those. Here is the true underlying architecture of the entire Microsoft Office suite:   a well-integrated yet loosely-connected collection of “things” that all play very well together.

When the Office suite ... when everything that Microsoft has ever published ... is viewed in this way, the opportunities for leveraging one against the other might become a little more clear. The “individual applications” that make up the Office suite are really just packaging. They are easy-to-think-of portals to the underlying functionality, exposing it for easy reach in a familiar way. But you can take that same functionality, bundle it in different ways, expose it in different ways, and make your own “applications” out of it. You can do other things:   you can put a live spreadsheet into a Word document or put an editable Word document into a spreadsheet cell. You can also blend the functionality of Excel and Access, as we'll be doing here.

“Wuduzitdo?” ... The Functionality of Excel and Access

To leverage the power of Microsoft Office components, we must look “beyond the box” and consider the underlying capabilities that each one of them exposes. Then, we devise a way to link these capabilities together to solve our particular problem.

What does Microsoft Excel do?

• It solves systems of possibly-simultaneous algebraic equations that have been expressed to it (usually) in terms of rows and columns of a square grid, in a “workbook” of similar square grids, any position of which may contain either data or a formula.
• When any square anywhere in the workbook is changed, all of the data relating to it is automatically recalculated.
• The package also includes a variety of charting and graphing tools which obtain data from the workbook and which can be (because they, too are “objects”) embedded into a workbook.
• Excel can present information taken from an “external data source,” such as a database, updating that information as the external data changes and applying it in formulas like any manually-entered source.
  • Notice that I am not talking about “importing and exporting” here! Import/export is a one-time static copy-operation, much like cutting-and-pasting from the clipboard. An external-data-source link is “live.”
• etc...

Okay then, what does Microsoft Access do?

• It is a “SQL-compliant relational database.”
• Allows the manipulation of arbitrarily large “tables” of information consisting of a fixed number of columns but an arbitrary number of rows.
• Is able to “join” information from multiple tables based on common values in designated columns, to obtain results drawn from both sources.
• Can sort, summarize, and group information.
• Works by means of queries, which tell Microsoft Access what information to obtain but not how to go about getting it.
• Supports not only its internal data-file format, but also “linked tables,” which can be any two-dimensional data source including other SQL databases, non-SQL database files from third-party vendors, and Microsoft Excel workbook pages.

I emphasized two functionalities that we'll be interested-in here, because these are ways that you can easily use these two applications together and use them very effectively without doing any Visual Basic® programming. Excel is great at working with flat systems of numbers, but it's rather terrible at grouping and at associating different things together ... two tasks at which Access, ummm, excels. But we can use Access queries to generate all of this and more, and we can obtain the information for those queries by linking to (say) our own spreadsheet. Then, we can use one of these queries as an external-data-source in our spreadsheet to put the results of our query right back into our spreadsheet. Notice that the information thus-obtained is “live.” You don't have to “save the file,” since these two products are not merely reading one-another's files. They're talking to each other, and working with each other, manipulating the same data you do whether or not you've saved it to disk.

A High-Level Step-by-Step ...

Here's the general procedure:

1. In Microsoft Excel:
   • Make sure that the spreadsheet has been saved, as you will refer to the spreadsheet by its file-name.
   • For ease of reference, place the information on a workbook page or pages, or in a named cell-range or ranges.
   • Make sure that the information is consistent. For example, don't mix data-types (string, number) in the same column. (All of the columns in a “database table” must have only one data-type in them.) You should give some thought at this point to exactly how you want to organize your data.
2. In Microsoft Access:
   • Create a new database (.mdb file) or open an existing one.
   • In the Tables tab, create one or more new “Linked Tables,” connecting to the Excel spreadsheet, each to the appropriate page or range. If you open one of these linked tables and view them, you should see that they are a live display, promptly reflecting changes made in the workbook.
   • Build queries in Access that obtain the information you want to show in Excel. (Obviously, this topic is much too big to discuss at length in a web-page article, but it isn't really hard to do.)
3. In Microsoft Excel:
   • In the Data menu, create an “external data source” corresponding to the Access database. Each of the queries you have created will be listed.
   • When you change the Excel spreadsheet data that is the input to a query, and possibly after you've clicked “Refresh External Data” in Excel, you'll see that the information in your workbook will be immediately updated to show the latest results of your query.

... To Be Continued in the E-Book

Well, I'm beginning to reach the limits of what I can easily do in a web-page article... but what you've read here is the first part of a chapter in a forthcoming e-book from Sundial Services, which will be provided (English only... unless you can help with that) in our store. I'll expand on this idea (and others) in a step-by-step guide and set-up some examples, all of which I simply don't have the space to do here. Please contact us to express your interest in the material (it helps smooth-away those many hours of writing...) and to give suggestions.

But let's close this out, for now, with a recap of the “food for thought” that I've provided here:

1. Microsoft Access is great at grouping, sorting, and joining. Excel, particularly, is not. But Access can draw information from an Excel spreadsheet in real-time and use the information as one of its “tables” through its linked-table facility.
2. Excel can reference an external database ... including a Microsoft Access database ... including one that is right-now linked to the current workbook ... as a data-source for any part of any workbook.
3. When one product “links to” or (as the case may be) “embeds” the functionality of one Office suite product into another, that's a live link. The information is current and immediately-obtainable even if you haven't saved the file, because the two applications are talking to one another, not just reading each others' files. (You might need to tell Excel to “refresh external data now.”)
4. The functionality provided by the various Office products freely cross the “box on the shelf” boundaries.
5. You can leverage this capability in many ways without writing a line of Visual Basic code. And if you do (it's not that hard, really...) you can go even further.

© Copyright 1996-2008 Sundial Services. All rights reserved. | Terms of Use; Copyright; Trademarks | Privacy Policy | Website Credits | Site last updated:   August 14, 2008.