28 March 2013

Dear Microsoft Access: I Love You

Five weeks ago, I was on a phone call talking about problems our users were having with an MS Access application. Up until this point in my career, I had successfully fended off “database” questions or issues in MS Access because I was not an MS Access expert. Now though, on this call, put together by one senior executive for another senior executive, I was the expert, whether I liked it or not. There was no avoiding it. I became that MS Access expert in about 3 hours and it brought back many memories.

It was December 1996, when I first met MS Access, and I was infatuated immediately. I was a young, neophyte data enthusiast, deaf to the typical accusations, mostly from SQL Server DBAs, that Access was “slow, unreliable, difficult to manage, limited in data capacity and easily corruptible”. On the contrary, to me, Access was fast, easy, capacious and non-defileable.

As with many youthful romances, however, it burned out quickly, a brief fling of little over a month. Around the same time in my life, I started hanging out with the cool kids, Informix, Oracle and SQL Server. I admit I was a jerk. I did not phone Access back after our first date; I would avoid eye contact when I passed it in the halls. When others would extol its virtues, I was scornful, “Really? You love Access? Why don’t you marry it, then? You can do so much more with SQL Server – Sha!” I was a heart breaker.

The call concerned the failings of a Critical Company Application (call it CCA) that was an MS Access database. I searched the Web feverishly trying to understand why MS Access would lock up and slow to a crawl. Finally, on page 83 of about 637,002 of a Web search, I found a pointer. The CCA was a single ACCDB file of about 250MB, distributed from a network share to over 40 users. Corruption was inevitable, apparently, as well as contention issues and lock files that would never go away. Compacting and repairing was as commonplace as candy on Valentine’s Day.

Reading about the cure for CCA was as dreadful as I had imagined. It was a world full of VBA code and SQL pass-through queries. Finally, I came to realize from a link on Microsoft own documentation there was a quicker solution, and one that had little to do with MS Access itself. All we had to do was to split the front-end application code, forms, queries and reports from the back end data and then publish a copy of the front end to each user. We did this and, magically, everything sped up and corruption ended.

Word spread around the company about my lunch date with MS Access. People were staring. It felt awkward. My boss, who engineered this blind date and knew I would be a pouty wreck, congratulated me on a job well done and joked that I should spend more time with it. I could hear the dreadful siren call of Access, beckoning me to come back to her, but I was the quintessential unwilling suitor.

“Nope,” I told him, “my dance card is full with my true love, SQL Server.”

Nevertheless, much as I tried to deny it, I found myself thinking wistfully about those simple forms and Jet engine, fast and powerful. A phone call cut short my reverie. It was my boss.

“Hey, remember CCA?” he asked.

“Yes,” I breathed, probably too quickly.

“Well, anyway, word got around and there are three more MS Access databases I want you to take a peek at.”

I blushed and looked at my SQL Server 2012 installation waiting there for me to configure and tune. I knew I would not get back to it for a while.

“OK,” I said, “Give them my number.”

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 19406 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share