Book Review: The Excel Analyst’s Guide to Access


The ‘Excel Analyst’s Guide to Access’ is especially good for Excel users who need an introductory but aggressive tutorial into the world of Microsoft Access. Read on to find out why Danny Lesandrini decided to keep this particular book in his own personal library.

When you have a hammer, everything looks like a nail. Since 1995, my hammer has
been Microsoft Access. I can do anything with Access … even things better
suited to Microsoft Excel. From what I hear, there’s a parallel universe out
there where Power Excel users feel the same way … about Excel. No matter which
universe you belong to, you’ll probably continue to get by just fine, but
there’s never been a better time to become, not an Excel Expert or an Access
Expert, but an Office Expert.

I recently read, and thoroughly enjoyed, a book by Michael Alexander titled The
Excel Analyst’s Guide to Access. Even though I’ve written a number of articles
on Excel automation and even though my standard code library has a nifty
Access-to-Excel automation module, I know I’m just a hacker when it comes to
Excel. So when people send feedback on those articles and ask for help, I feel
only moderately qualified to assist. I figured this book would bridge my
technology gap, and I was right. I highly recommend it for Access developers
who need to move data programmatically between Access and Excel.

Strictly speaking, the book wasn’t written for Access developers who want to
learn more about Excel. As the title suggests, it’s for Excel Analysts who
would like to augment their Excel skills with some Microsoft Access
programming. It’s especially good for Excel users who need an introductory but
aggressive tutorial into the world of Access.

Right Tool For The Job

The first step is to know the tools and their limitations. Many managers,
accountants and analysts successfully use Excel for data analysis without ever
reaching what I’m calling "the wall". Others become frustrated when
their data demands exceed what Excel can realistically deliver without a
performance hit. According to the author, Michael Alexander, the following
factors should be considered when choosing between Excel and Access for a
particular data storage task.

Where Excel Excels:

  • For day to day data requirements
  • With interactive, impromptu data analysis
  • When data sets are reasonably sized
  • For manageable business logic/calculations

Excel Hits ‘The Wall’ at:

  • li> Scalability of data
  • Separation of data and presentation
  • Transparency of analytical processes

The
author admits that Power users have learned how to work around the limitations,
but that’s exactly what they are, "work-arounds". For example, Excel
2010 increased the maximum row count from 65,536 rows to 1,048,756. That may
sound like a solution to a limitation but the author points out that in Excel
the entire file must be loaded into memory and calculations performed before
it’s delivered to the user. Each time something changes Excel must reload the
entire spreadsheet and refresh the calculations.

Access, on the other hand, need only load into memory the records that are to
be displayed, no matter how many rows the table contains. So if performance and
scalability are a requirement, Microsoft Access will probably be the winner.
Access also wins when it comes to keeping things in their "buckets".
Data is in the Table bucket, presentation is in the Reports bucket and business
logic (calculations) can be grouped into the Modules bucket in the form of
custom VBA functions. For some Excel documents this is overkill, but when a
spreadsheet gets complex, it’s time to start looking at Access.

Fast and dirty Access tutorial

The
book begins with Access basics, like how to create and set properties for
tables and queries. As a bonus, there’s a list of common query errors and their
meaning. This is especially valuable for beginners and something I haven’t seen
before in this sort of book.

Of course the various types of queries are explained (Summation, Append,
Parameter, etc.) but he also explains how VBA functions may be used in queries.
One particularly valuable and concise expression returns the last day of any
given month:

LastDayOfMonth: DateSerial(Year(Date()), Month(Date()) + 1, 0)

The trick is to set the DAY argument of the DateSerial call to zero (the day
prior to the 1st of the month). I know I should remember this trick but every
time I need the last day of the month function, I find myself searching the
internet for it. I hope that this reference will increase my odds of finding it
the next time I forget the syntax.

Query is NOT updateable

In addition to the list of query errors, there’s a summary of why you might be
getting the famous "Operation must use an updateable query" and
"This Recordset is not updateable" errors. He lists eight reasons why
the query you constructed may not be useable with an update command. This page
is worth photocopying and pinning to my wall.

Quality Advice

I was impressed with the practical advice the book offers. I’m going to reveal
one such suggestion for creating what he calls "In cell charting"
visualization. The code is simple, but the results are impressive. The screen
shot of the query result and the code to create the [Graph] column are below.
The [Check] column is just an extension of the principal implemted in the
[Graph] column. This little trick will come in handy.

Graph: String(Count(*), ChrW(9608))

Excel Analyst's Guide to Access in cell chart

Automate Microsoft Office

Automation is discussed from both directions: code in Excel to automate Access
and vice versa. Honestly, it’s not my code so I won’t be representing it here,
but suffice it to say that it’s concise and valuable, just like the sample
query-trick described above. The automation code includes functions to perform
the following:

  • Import Access data into Excel using custom SQL statement
  • Append Excel rows into Access using automation
  • Open Access objects (queries, reports, etc.) from Excel
  • Get Excel data from within Microsoft Access
  • Put data to an Excel worksheet from Access

One chapter touches on automation with Word, Outlook and PowerPoint, but these
are only briefly covered. An entire chapter is devoted to integrating with XML.
It’s a basic primer on the topic but probably enough to get you started playing
with it and to decide if it’s the right solution for your particular needs.

Useful Appendices

The appendices are actually quite useful. They include the following topics:

A. Access VBA Fundamentals
B. Understanding and Using SQL
C. Query Performance, Database Corruption
D. (Excel) Data Analyst’s Function Reference

Conclusion

It’s safe to say that I have read just about every book that’s comes out on
Microsoft Access but I keep precious few of them in my library. (I like to
travel light.) When I finish a book, I usually pass it on to an Access
programmer buddy or a workmate. That was my plan when I started reading this
book, probably because I considered it was a primer for beginners, but by the
time I was a little more than half way, I’d decided to keep this one on my
shelf.

What makes a book a keeper? A book worthy of shelf space must contain useful
reference material that …

1.
I can’t keep in my head
2. is difficult to find online
3. is accurate and concisely presented

I know an awful lot about Access, but when I get the "Query not
updateable" error I always scratch my head and wonder what I did wrong
this time. I love Alexander’s list and I know I’ll use it often. Connection
strings and automation language is another thing I don’t seem to be able to
memorize. I’ve written this code dozens of times, but I always end up looking
it up in old snippets of mine. As a result, I tend to implement the same
methods I have used for years instead of considering new options. Alexander’s
book gives me additional options and I’m now looking forward to the next Excel
automation project with enthusiasm.

Publisher: Wiley
Author: Michael Alexander
ISBN: 978-0-470-56701-2
Paperback
624 pages
April 2010
Price: $39.99

Additional Resources

Microsoft : Using Access or Excel to manage your data
Workplace Life : Access Vs. Excel: When to Use Excel
vb123.com : Getting Data From Access Into Excel

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles