I recently started a short project that involves a fairly large Access DB (large for Access anyway). My first job out of college was primarily as an Access developer, and of course going into it I knew very little about DB’s in general other than the brief (very brief in retrospect) Oracle class I had in college. At any rate I eventually outgrew Access, and it’s been several years since I’ve had a chance to use it. I’ll even go as far as saying I was actually looking forward to playing around with it again, as it would be somewhat of a trip down memory lane for me. It didn’t take too long however to realize that one probably shouldn’t assume they’ll find the same DB design principles in an Access DB that they might expect to see in SQL Server, ORACLE or other top tier RDBMS’s. I must admit I was a little let down when I opened the Access DB for my current project and found that it had no relationships or primary keys defined for any of the tables. One can imagine that as I dove deeper into the DB, things didn’t get much better from a design standpoint. Users were complaining of duplicate data, missing data, and execution times for some of the macros were approaching unacceptable durations. All this got me thinking about how an Access DB (Critical to the business, and currently inoperable) managed to get where it’s at. I imagine the story usually goes something like this:

A business unit has a budget, and a need for 15 IT related projects. The business contacts their internal IT or external contractors/vendors for an estimate. The business unit gets an estimate back, and it turns out that they only have enough money in their budget to complete the 5 most critical projects. Constraints being what they are, they’re forced to put the other 10 projects on the back burner. However, just because those projects are on the back burner, they’re still just as needed by the business as they ever were…and as it turns out, necessity is the mother of invention. At some point someone decides they’ve had enough, and gets motivated to take the situation into their own hands. We’ll call him “Aaron the Jr. Access dev”…or maybe just Aaron for short. Aaron goes out to the book store, buys an “Access for Dummies” book (which I actually owned myself at one point), and decides that he’s going to develop THE application that he has been longing for since the day he started. Aaron skims the book over, starts throwing the Access DB together in a “learn as you go” manner, and in a few weeks he’s got something that is really useful to him. It runs smoothly for the most part and any glitches that do happen, Aaron now has enough skill to fix. Aaron is proud of his accomplishment (as he should be) and he shows his boss and a few other people in his business unit what he has created and they want to use it too. Eventually Aarons boss thinks…hey, I still have these 9 other I.T. projects that I didn’t have enough money to get completed, I wonder if Aaron could make something in Access that would fill those projects needs too. Aaron jumps at the opportunity and before he knows it he’s probably doing Access work full time, and supporting a dozen Access DB’s covering several areas within the business unit.

Flash forward a few years. “Aaron the Jr. Access dev” no longer works for the company for whatever reason…I could speculate but I won’t. All types of people are still using Aarons Access DB’s though, to the point that they’ve become a critical part of how the business unit functions. Shortly after Aarons departure, things started behaving strangely in the Access DB’s. Business processes have changed a little bit in the months since he left, the DB is taking forever to do things it used to do almost instantly…and finally one day…it completely stops working.

Maybe a few people in the business unit have done a little Access work in the past, but nothing on the scale that Aaron had done. They take a look at one of the 60+ step macros in the DB and start trying to figure it out, but quickly report that they can’t uncover where things are going wrong. The immediate realization is that nobody really knows how to support these monsters. Internal I.T. is called to resolve the problem, but I.T. didn’t even know all these Access databases existed, and they don’t have the bandwidth to put someone on the project full time for a few months anyway. To make matters worse there are no original requirements, or any documentation at all for that matter. Aaron was the only one who knew anything about them, and he’s gone.

This is where I probably come in, because you not only need to get this thing fixed fast, but now you’re thinking you need to get these things out of Access and into a more secure, scalable, controlled environment. Only now you’re paying for it 3 times. Once for Aaron to build it, once for me to fix it, and once again to have it rebuilt to I.T. standards….ouch.

Now the point of this story was not to trash Access, or Aaron the Jr. Access dev. I love Access, it’s a fantastic application. I love Aaron the Jr. Access dev. because I used to be him, and you’ve got to start somewhere. The point of this story is that home grown Access DB’s are not a good place for business critical applications in large corporations.

· All the business requirements are usually locked up in one persons head and never documented.

· It’s really risky to keep sensitive data in Access, as anyone could make a copy, stick it on their thumb drive, and walk right out the door with it.

· Are you running regular automated backups on your Access DB?...probably not, because IT isn’t involved.

· If there are upstream systems the Access DB uses and something changes in one of them, would anyone even know to notify your business unit?

· Aaron the Jr. Access dev.’s DB design probably leaves something to be desired, and probably isn’t going to scale very well.

So before you let “the little Access DB that could” grow completely out of control…beware.