In Defense of Access...Sort of (the good parts at least)

In my real job I use Microsoft Access a lot; some would say that I use it too much, that Access is an archaic backwards monstrocity that should be dropped from the Microsoft Office Suite, that I should be running on SQL Server with a .NET frontend managing things, that Access isn't powerful enough, isn't fast enough, isn't good enough for anything.

 

In fact, I think it's easier to think of reasons why Access sucks than to think of its benefits. And as I delve deeper into Access development, I find myself getting more and more indignant when people start shitting all over my development platform du jour.

 

Don't get me wrong, if I had .NET and SQL server again, I'd probably use them, but I wouldn't be as productive even if everything were shinier, faster, and more "professional" from .NET.

 

Herein lies the gem that is Access: it allows for fast, useful automation of day to day office activity. It integrates into other Office applications, and after six years of tinkering with it (with much of that time under a really great mentor), I can make Access do all kinds of things that would be a lot more complex to replicate under .NET.

 

Currently I have databases talking to Outlook, Excel, and Word; making documents, sending emails, creating attachments, all automated with very little user imput needed.  And this is just an iceberg's tip of the things I've done/can do.  Access as a development platform gives me a lot of really great tools with minimal setup and configuration.   And I can extend all of that with Standardized Modules containing all of my most-often-used code.  Currently I have 12 Standard Modules for each database containing functions from Basic Database connectivity to Form manipulation, to automated emailing.  

 

The only thing that really holds Access back from being a real contender is Microsoft. Every version of Access I've worked with has had some mind-numbingly stupid implementation Fail on Microsoft's behalf. Want an example?  Let's talk Synchronizing.

 

Prior to Access 2007, there was a feature called Replication.  It let you create a replicant database, where you could go offline, make updates and then come back later and synch in with the master.  In pre-Access 2007 days I never really had a need for it, but it was always there.

 

With Access 2007 Replication is supported only in Compatability mode (ie .mdb/mde format).  My need for Replication didn't come about until  after I upgraded all of our databases.  And so some quick searching revealed that the "new" way to handle replication is through SharePoint.  Cool. We have that. And everyone in Corporate wants us to use it (because well it's pretty useless).  So I tried it out.

 

Small database, only a couple of tables, and uploaded it to SharePoint.  The first thing I found was that Databases run like Garbage on SharePoint.  The small table limit (2000 records or less for optimal performance) mixed with our not-so-great network meant that, well, access times went way up.  So I turned to synchronizing.  Immediately I saw two things: performance went up, and the database started to unravel.

 

Yes synchronization with SharePoint let's you work Offline with much improved access times, BUT

• When you upload a Table to a SharePoint List, SharePoint confiscates your PrimaryID and Renumbers it from 1 -- So I hope you didn't plan on using that as a foreign ID anywhere Else
• You can't access that PrimaryID any longer for anything but reading -- So don't bother trying to re-renumber your ID back to what it was
• Referential Integrity? Yeah, SharePoint doesn't deal with that bullshit (Seriously, what Database program needs Cascaded Updating and Deleting between Primary and Secondary tables?) [Note Severe Sarcasm for you non-programmer readers].
• Validation Rules? Nope.
• Default Values? Nope.
• Additional Unique Fields beyond the Primary Key? Nope.

 How could you NOT be jumping out of your order SharePoint 2007 as your new Online Table Source? Oh wait. Because it's fucking useless.  Especially since it's the Only supported form of replication for .accdb/.accde files. 

 

Oh and get this: Microsoft's Solution: Go back to Access 2003. Nice. Why would I, or anyone want to depreciate to 7 year old software? Isn't that against the general plan of things?  But then again, Microsoft made Vista and Me and the Early versions of Win98 and XP both sucked too, so I guess I should know better with whom I'm dealing.

 

Oh the light at the end of the tunnel though: Microsoft will support Referential Integrity in SharePoing 2010...too bad our shop won't update to that until 2012 at the earliest.

 

My current solution: Hombrew Synchronizing.  It'll be awesome when it's done, but it'll be a bit of a beast to retrofit the databases for it. 

 

The point of all this is this: Access, since it is part of Office, beckons lots of know-nothing users into fucking around and making truly hideous databases, but those of us who really work at it, can  and do make some pretty powerful applications, with our only limitations being that of the poor design decisions on Microsoft's behalf.  So before you start dropping your condescention on the next Access Developer you see, take a moment to find out what's going on under the hood and reserve your words for the people that make us look bad [ you insensitive clod(s)].