MS Access as Enterprise Software?

Question

Something that I often run into with my users is their desire to aquire solutions quickly means that they sometimes have said "Heck, I'll just roll up my sleeves and do it in Access - it's installed on my desktop".

Sometimes, we're lucky and the person that creates the Access database back-ends it to a SQL Server, so at least the mdb file issues that often come up aren't an issue.

However, it is my opinion that rolling out an Access front-end to a SQL Server database as an enterprise solution with thousands of users, and hundreds of thousands of rows is still problematic.

What are your opinions on this? What are some of the potential pitfalls?

OR

Is this a perfectly acceptable, stable, maintainable, and robust solution?

Answer 1

I've worked with this scenario a great deal. In fact as a consultant/developer Access front end SQL Server back end has been a significant part of my bread and butter work over the past 10 years. Which doesn't mean I like Access ;-)

Up until the common adoption of AJAX it was a perfectly reasonable solution. And there's still vast numbers of small to medium sized applications put together in Access out there that run bespoke business systems perfectly happily and I doubt it's going to go away for the next 10 or more years - indeed Access/SQL is probably going to be the Cobol of the 21st century. If you're working on a 'green field' site then there is now virtually no excuse for deploying Access when building from scratch - but if you do inherit an existing application then the costs of a rewrite may not be worthwhile and difficult to pass with the users.

Access does have some advantages that are still significant - and can present problems if proposing to convert to a web app

1. It's quick. For simple CRUD work it's as fast to write and deploy as any other realistic solution.
2. Built-in reporting is easy to get running and remarkably powerful given the system. It's usually pretty easy to create and deploy new reports for users on demand.
3. It integrates well with Office. This one tends to be the show-stopper when looking to move Access apps to web-apps. It's extremely common for a 'department-size' Access application to tightly integrate with Outlook, Word or Excel - and often all three. This is the major problem when dealing with real-world situations. It's very easy for coders to underestimate the importance of this for everyday usage of such systems and the imposition of even a small degree of additional hassle for the users will generally be met with much resistance - often enough to completely scupper the project.
4. If your working with a reasonable sized department - a dozen people or so - it's quite common for there to be someone in the office who fancies themselves as a bit of a computer wizard. These people can be a major pain if handled incorrectly, but equally can be a major asset. If I have such a person I will try to get management to send them on an Access course or two so they can write simple queries and reports, and set up a separate Access application for them which they own which has appropriate (restricted) access to the SQL database. You can then trust this person to handle producing simple reports and the like for their colleagues. This can be a real win-win - you gain someone who is on your side and will use you as a mentor - a ready-made advocate for you in the department - and they keep the grunt report work out of your hair. They gain a lot kudos and job satisfaction - and even a potential career path. It's far harder, well near impossible, to do this kind of thing with any other system but Access.

Main practical disadvantages

1. Deployment can be a nightmare. Generally if you have a very tightly defined environment - a small company, single department, citrix based or distributed with an IT department that closely controls it's PCs then you're fine. Deployment as a commercial app across multiple companies - well only if you can charge significant maintenance (been there).
2. Code does not scale. Access VBA code, even when written by a professional has a strong tendency to rot into rancid spagetti. It's quite common to end up with an Access application that was easy enough to maintain, but gradually becomes unmaintainable as dependencies multiply.

So I'd say Access still has a place, and it's use is defendable in many real world situations, but increasingly it's better to choose a more modern solution if circumstances permit.

Answer 2

We have built such a solution (Access front-end, SQL back end), with now something like 80 users, millions of rows replicated between different countries, more than 100 000 updates a month. It works fine. I think the main mistake about Access is to consider it as a tool made for amateurs to develop applications. It can work this way, but keep in mind that amateur development will give you amateur applications, while professional development will give you professional results.

A quick list of its advantages, problems and limits:

• It's free for the final user, thanks to msAccess runtime
• It works with the free SQLServer Express, and not so expensive SQL Server Enterprise.
• It's quick, specially when dealing with forms
• It communicates very easily with other Office apps, which are still enterprise standards
• You can manage its interface to be so close to Office standards that using it can be very intuitive, making people happy (I talked a little bit about that on my blog, need to be updated!)
• On a large scale, you have to think about the best way to distribute it to your users. This issue can turn into a nightmare, as noted by #Cruachan, but it can be solved by building and distributing msi packs for example. Such msi packs can also contain all your external references such as 'added' dll, ocx, tlb files (report dll, activeX scanner controls, etc). We had a few words on this here.
• When distributing an updated version of the mdb file, you can have a common network folder holding the new mdb/zipped file that clients will check/update at startup. Your clients should have the possibility to reinstall a previous version of the mdb file. Upgrading becomes then easier than installing a new .exe file.
• You have to set a version controlling system. Please check here for details.
• You must be very strict on your code organisation. One of our basic rules is for example not to have any specific code at the form level. Please check here on this subject.
• I didn't find any problem with VBA code scaling, as noted by #Cruachan. If professional coding rules are implemented, there will not be any unusual code scaling issue. As an example, our application is now working really fine with more than 180 different forms, and still growing without any problem.

As a conclusion, our main problem with Access is an image problem, where Microsoft still let people think that Access is here to give them the possibility to develop real sofware in 10 lessons ... and professionals, who know that is not possible, view it as a amateur tool for amateur development, looking down on ms-access users as boring low IQ red-necks.