Kill Access With Fire

I want to be very careful how I phrase this.  We can't have any Redmond-funded lawyers circling around me like hungry sharks because my hyperbole is mistaken for meat swim-trunks.

So let's keep it personal - I Hate Microsoft Access^{TM (C) LMNOP}.

It is a blight on civilized companies everywhere.  By being included in the Office Bundle/Suite/Tollset [spelling intentional] MS-Access is available to entirely too many aspiring tech-savvy "business" people.

It happens slowly over time - first some good intentioned person needs to manage a lot of data.  By a lot we mean more than the spreadsheets that they would ordinarily (ab)use to store them.

And so they make a rudimentary table and load a lot of data into it.  This works so well that they start doing it all over the place.  Quickly however the relationships between the tables become convoluted.

This is where someone who truly understands database design should be brought in to fix things before proceeding.  This is never the case however and so slowly does a swamp start to grow that will need to be drained later.

I have worked on projects in the past where we have tried to leverage automation tools to convert the database structure of Access files to another format - for example Oracle APEX.

Another example of such a tool that I recently discovered would be : http://www.bullzip.com/products/a2m/info.php

But the problem of course is that there is so much inherent functionality^* in Access that it is neigh-impossible to capture it all in another tool on another platform.  Just the integrated ability to send email through Outlook alone represents a huge functional gap for any other system.

You would at first think that the sorts of things that would be made would be simple and thus easy to reverse engineer, but as it happens there is an inherent dogged spirit of innovation in the people who are using these tools and trying to get everything that they can out of them and thus Yes, people send email from Access and many other things like making forms with buttons to run import scripts, etc.

Moreover those people will live with such a solution for a very long time before being compelled to change.

The challenge for IT is that they must provide not only a new replacement toolset that can accommodate all of the preexisting functionality but also do so in an enterprise-scaled solution.

The problems are always that such a replacement is very expensive & almost always inflexible whereas Access is extremely cheap by comparison & can be modified on the fly.

The big problems of course with MS-Access are that it does not scale well nor is is compatible with anything else or even with itself across version numbers.

There are severe limitations to the amount of data that can be stored in a single file and also with the number of simultaneous users that can access the database simultaneously.

MS-Access is not even compatible with MS-SQL Server for direct import.  The syntax varies wildly  between the two.

So in short - MS-Access databases start off sweet but if you are successful then they will end in bitter tears.

_
^* - You might think "too much functionality is a problem?" But it is more of a question of the types of functionality since Enterprise databases do not generally include for example email functionality.