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 50GB – for 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!
Yes, 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.
Yes, the application is connected via RS232 to a warehouse forklift floor scale, and updates in real time.
Yes, 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?
A 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.
The 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.