I Cannot Believe How Buggy MS Access Has Become!

I make no secret of it to anyone: I’m not one of those “snooty” DBAs who turns his nose up at MS Access. I’m a big fan of MS Access. I’ve used it extensively since version 1.0 when it came on four floppy disks. But not for data storage.  I used to be able to deliver complete solutions using just Access, but databases have grown.  And the 2GB limit that used to look like “plenty enough” a few years ago is now woefully inadequate. 

Unlike my last job where less than 1Gig was the norm (waaaaaay less), the data we move at my current job is more like 50GBfor a simple, an entry level database. And this time, the consumers of this data is no less than Congress and Executive Branch.

In other words, The data I move now would mow down Access like a ‘possom in the middle of Dallas LBJ Freeway at 5pm.

No, I use it as an application development tool. And I’m good at it. Damn good.  If you jerk out JET, and replace it with SQL Linked Tables, you get all the bennies of SQL Server, with none of the limitations of Access.  And if you create Views as much as possible, you can force the work involved in filtering and sorting onto the server and minimize the network traffic created by those operations.  Back in the late 90’s, Microsoft’s answer to PowerBuilder was never Visual Basic.  It was Access, and they should have pushed it harder.

In other words, Access is a very powerful platform, if used right, and I wring the most out of it.

Here are some examples. Yes, they are pure MS Access Applications.

Yes, that’s a live weather map sourced from a local TV Station, embedded in an HTML control on a subform embedded on a tab page of another parent form!


MP1Yes, the drop down in the user preferences gives them a list of printers that are actually installed on their own machine, and network printers too.


MP1Yes, the application is connected via RS232 to a warehouse forklift floor scale, and updates in real time.


MP1Yes, the application is connected via TCP/IP to laser leveling detectors in the tops of the silos and the scale updates in real time.


And, yes, in all the above applications I did steal the look-n-feel of the MS Office website. (Did you notice there’s two different company’s apps shown… Consistency!)

 

How about something more traditional, and wickedly complex?

MP1A very complex application (these are only two of many dozen forms in this system) involving a LOT of date/time math. Cripes, I hate date/time math. But I’m pretty good at it now after doing this app.


MP1The TreeView on the left implements a custom right-click, context menu, even though people said it couldn’t be done in Access. The whole system is a set of forms inside subforms, on tab pages, embedded on another form which is a subform of another. Very complex, and yet still mostly databound controls.


All this is done using MS Access 2003, usually (but not always) with SQL Server for data storage.  Sometimes as an ADP.  Sometimes with SQL Linked Tables.

Now, there’s this latest project. And this one I have had to do in Access 2010:

I’m only posting all this to say this:

I am far, far from being an Access Novice.  I know all the traps and tricks to avoid database corruption.  In Access versions 97 and before, corruption was a pretty common occurance.  In Office 2002, and MS Access 2003, they pretty much figured out all the problems, and database corruption became really pretty rare.

So stable was 2003, that when Access 2007 came out, I avoided it.  Partially because the new ribbon menu absolutely sucks, but also because I had no compelling need.  But now, at my job, I have to use Access 2010.  And while I am happy that I’ve gotten accustomed to the stupid ribbon menu, I am very UNHAPPY that database corruption problems are back with a vengeance. 

I do a lot – almost ALL of my work in modules: CODE.  And simple compilation errors can corrupt the database.  Simply deleting rows of data from a table can corrupt the database.  Simple runtime errors that used to just say “Invalid Data Type” and popped open the debug window, now they corrupt the database! Compact and Repair usually works, but even that doesn’t always do it. 

Basically, Access 2010 – as far as stability is concerned, is like going back to Access 2.0/97.

Microsoft, PLEASE FIX THIS!

The good news is that I know they will.  it may take time, but MS listens to it’s customer base and reacts accordingly, so I know this will eventually be fixed and things will go back to their normal stable (or at least predictable/reproducable) state.

Advertisements

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in Procedures. Bookmark the permalink.

9 Responses to I Cannot Believe How Buggy MS Access Has Become!

  1. Dave Bolton says:

    Wow, I thought I was the only one that thought that Access 2010 was buggy. Maybe buggy is too strong except that Access 2003 is so stable. Bravo for pointing this out. I have used Access 2003 for many projects and am at my wits end with Access 2010.

    I am working on a couple of projects that require me to use this version of Access and it is just too prone to corruption for me to turn over to end users. I am redoing one (maybe both) in Visual Basic 2008. Of course I would rather do this with Access.

    • combatdba says:

      Agreement all around. My take on Access goes all the way back to the late 90’s. Access 97 era. To me, Microsoft kept trying to compete with the RAD tools back then (such as powerbuilder) by trying to push Visual Basic. But VB was never the answer to PowerBuilder. It was, and should have been MS Access.

      It has a top notch form designer, source code, queries, all in once integrated package. It’s report designer can compete head on with products such as Crystal Report. And it’s been solid and stable for over a decade.

      Then Version 2010 (and 2007 before it). Bah!

    • combatdba says:

      Hey, also… I sometimes sneak in Access 2003 under the radar by installing the 2003 RunTime and running my app that way. 😉

  2. Thierry says:

    Could not agree more on the “bugginess” of Access 2010. After facing yet another “Microsoft Access has stopped working” I googled “Microsoft Access very buggy” and ended up here 🙂

    This can be indeed very frustrating as it often forces me to decompile the app.

    Hopefully, Microosft will release a proper SP soon!

  3. ronnie valero says:

    I use access 2010 no SP as front end to sql server 2008 express R2, so far ive never have had such corruption experienced. Access is next to none, ive try lightswitch but still I lean on access because of it’s killer report writer. We can only have our final billing if clients are ok with the report output. just my 2 cents…

  4. Jonathan says:

    I totally agree. I work in the Electricity industry, and use access to store / manipulate grid data and study results. Thanks to 2010, I’ve made corrupt forms that won’t save, I’ve made db relationships that linger even after being deleted, etc, etc. I’ve only managed to survive by keeping backup copies and finding workarounds.

    • combatdba says:

      Microsoft needs to introduce a “Developer Version” of Access. This version will not contain all the “stupid user helps” that they are trying to push in the RTM product.

      No “table design features” in table view mode (ie, No “Hey, you wanna ADD a brand new column to this table you’re looking at? No problemo, just click here!” stuff)

      Basically, I liked 2003 because it gave me complete and utter control over the look, feel, and usability of forms and reports. 2010 is trying to “help out” too much, and it’s helps are getting in my way as a developer. “Hey Microsoft… I’ve already thought of that and built it into the menu system…. Leave it to me, thank you very much!”

  5. D Morse says:

    I’m not nearly as experienced in Access development as you are, and have mostly been using other tools prior to my current role, where the primary development tool is Access. Our shop is having some data integrity issues with Access 2010, and while it gives me a bit of comfort to know we’re not the only ones experiencing this, I’m also frustrated by some other quirkiness in the form design area. My area of expertise/emphasis is user interface design. I save a copy of an object (form) under another name, and the label positions in the copy are different than the original form, and it also resizes the (supposedly fixed-size) window. What’s up with that???

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s