Book Review: The Excel Analyst's Guide to Access

June 7, 2010

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers