Click here to monitor SSC

Dear Microsoft Access: I Love You

Published 28 March 2013 10:55 am

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.”

21 Responses to “Dear Microsoft Access: I Love You”

  1. dmytro.andriychenko@sage.com says:

    I totally share your “feelings”! My experience of DBMS has been rather varied and it actually began with hierarchical mainframes and DOS-based Clipper and Paradox. But it was Access that made the whole thing truly productive for me in terms of data integration and analysis :-)

    Now that I am Certified SQL Server professional with many years of experience, I still look back at those good old Access days with a very warm and cosy feeling. Most of my IT colleagues totally shy away the tool in their educated snobbery, but I just do not let that affect our occasional but very fruitful relationships :-) . It is those poorly educated abusers who give Access the reputation it does not deserve.

    Use what works best for the situation making sure corporate IT strategy is maintained, this is what I believe in. And it the latter relies on Access, than so be it.

  2. callcopse says:

    There are three good circumstances to use Access in my book.

    The first is a split – separate data and interface – internal application that may need a few tweaks by untrained users. For an application of modest needs this works well. I’ve seen this model stretched to 100 users with lots of data input and it can keep going even if it’s not ideal.

    Secondly it’s pretty good at wrapping up a chunk of data for transport. We frequently use it as a transport mechanism, receiving automated ftp for example of financial data. This can then be used directly or sliced and diced.

    Thirdly I like it as a quick and dirty utility knife for transforming data on a one off basis. For example when mapping data from system A to system B when you know there are numerous problems, Access allows quick construction of VBA transform functionality to re-map. Particularly useful when developing a new system that wants to be able to make use of historical Foxpro data or similar, where you might not be able to count on relationships you want to impose – Access makes it simple to cobble something up that is repeatable if not pretty.

    It’s a good horse for certain courses…

  3. Grant Fritchey says:

    NO MAN! Step away from the light. Turn around and come back to us.

    Ha!

    If it makes you feel better, I’m building some Access forms for a Boy Scout Eagle project that needs a database (using Azure SQL Database for storage) and a front-end. Access is still pretty easy to use.

  4. jwiley says:

    Did your manager ask how you to integrate Hadoop with these access databases? ;-)

  5. Rockstar says:

    It’s like I don’t even know who you are anymore.

  6. Jim_Huffman says:

    Hello Rodney.
    I feel your pain – Welcome to MY WORLD!

    I escape every so often and then suddenly,I’m back again. Sorta like Ground Hog Day.

    Good luck.

  7. Bill says:

    I worked with an app that was in Access 97, it had two tables in it that had over 1.5 million rows in it. We had about 25 users that ran the app (separated from the db) and while it worked, it could slow to a crawl in a heart beat.

    The pains we went through to keep that thing running while we rewrote the whole thing in .NET with SQL Server as a back end.

    It was a relief to finally get off of Access and into something that didn’t struggle just to stay alive.

  8. ergoface says:

    Access is the Rodney Dangerfield of MS apps. It gets no respect, and yet it is vital for millions of users and has an enormous developer community. Yet, because it is misused by certain users, IT in general and most DBAs in specific treat it (and anyone who admits the like it) like toe-jam.

    Sad.

    Used in the right context with the right security, it is an excellent app and really deserves better. But I doubt the snobbery will end any time soon.

  9. DrTechnical says:

    I have used the Query By Example (QBE) feature of Access more times than I want to admit to quickly try out query expressions to see what works against my linked tables. I have access to the SQL Server Management Studio as well, but I always seem to come back to the Access QBE when I want to construct a query. Pasting the SQL View code into another application makes short work of quite a few challenges.

  10. simondoubt says:

    I, too, have *some* fond memories of Access – it was the tool that I used to learn how databases work. I still think it can fit the bill for form creation, for very simple apps.

  11. greentian says:

    Although Access is not my favorite database tool, it has its place. (I actually learned pre-relational and relational database techniques on an IBM mainframe. Some years ago.)

    I am now running a database of over 100 GB of data on a SQL Server. This system uses T-SQL stored procedures and functions for most database work.

    However, the UI makes productive use of the Microsoft Access UI features in ADE format. (Yes, I am aware that is going/has gone away.)

    My favorite (uncredited) quote is: Ninety-nine percent of Access developers make the other 1 percent look bad.

  12. sdmcnitt says:

    I was going with DB2 when I first met Access. She was so young then –she listened only to macros and I only to COBOL.

    Her parents worried that I would corrupt her.

    But she was hot. As she got older she learned VBA and became very extensible.

    I was attacted by how many simultaneous hookups we could make. She would get all dressed up in some sexy forms that barely hid her data.

    But it was all so dirty. She really had no source control and I stopped quickly when we started talking replication.

    I see her sometimes. She still looks pretty good. Her sister Excel runs our entire Business unit.

    I think she is running my bank right now.

  13. rogerthat says:

    Do you remember Paradox? Now there was an amazing object-oriented contender for Access!

  14. Timothy A Wiseman says:

    First, this is definitely the funniest post about Access I have ever read.

    But I think Access is often much maligned for no reason. It works quite well at what it does. It is simple enough that non-programmers can do at least basic things in it, and it is well documented so that you can learn it quickly and begin doing more complicated things. It is a rapid development environment, which for a huge number of projects is more important than the raw power it commands (or in this case, lacks).

    It gets into problems when people try to make it exceed its limits. But for small projects that might need to be (at least initially) developped by non-programmers and serve just a few users, it works very well.

  15. tcartm says:

    For me I spent too much time in Access (years and years) so my climb to higher heights in the real world of SQL has been a struggle but I welcome any tips or book suggestions that can get me acclimated to the changes…

  16. Keith Rowley says:

    I loved MS Access when I first started using it. I have not spent any time with it since I moved over to SQL Server but I still think it could have its place. My problem these days is that so few people shell out for the professional version of Office that most computers don’t have Access installed.

  17. jerryhung says:

    I’ve stayed off MS Access as DBA or Developer, and fingers crossed to continue this way

    I do have people who have Access front-end (fancy dashboard) that talk to SQL Server databases we host

    I’m surprised it’s still included in Office 2010/2013

  18. RikGarner says:

    Microsoft Access is the Swiss army knife of application development in the MS world. There aint nuffin an Access database won’t open.
    Definitely my desert island application.

Leave a Reply