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

If you have the choice, no.
That being said, there are situations where it may be alright. One situation is if you never ever plan on updating the access application. If it is installed for thousands of users, you may run into problems getting all of the client apps updated.
You are much better off making a web front end... Although Access makes the multiple master-detail forms easier than anything I have seen. Even Oracle Application Express, intended to compete with Access, cannot do everything that Access does.
My advice is that if you are a programmer, you can make an asp.net app that will do the same job in a much more scaleable, maintainable, nature.

Answer 2

12-15 years ago this might have been an acceptable practice (not really advisable, but acceptable) but nowadays its unforgivable. There are so many more scalable and distributable solutions that Access should be the last thing to cross somebody's mind.

When you say Access as a solution what comes to my mind is a simple, 2-3 table application that some marketing employee put together, not a real developer. If the marketing guy had a really good idea then perhaps the development team should look at it (I'm assuming there is one sense you indicated there may be thousands of users), refactor it to a better platform (intranet or winforms distributed via ClickOnce, etc), and then deploy it.

Back in the early 90s I was an Access developer--even had a MS certification. I built dozens of "Enterprise" apps (meaning 10-15 people used them). Those days are gone, IMO. There are easier solutions to build, deploy, and maintain nowadays.

Answer 3

Sadly, I have quite a bit of experience with this. We built an entire product around Access Forms tying into SQL database. Honestly, the performance wasn't an issue - it really is the normal db connection type scenarios that you'd have to be concerned about with any client/server app. In our case, the original developer knew tons of "tricks" in Access, and did things like databinding drop downs to stored procedures. Oh, and the awful triggers. Awful. As in, 45 triggers firing per update awful.

The tables we worked with did indeed have millions of rows of data, however typically the roll-out was to tens or hundreds of users. I'd imagine that any effort going out to thousands of users would benefit more from a custom development so that you can do things like build the software correctly, support it from a performance and development perspective, and build automated deployment options (MSIs or ClickOnce, for example).

So, I would not say it is a perfectly acceptable, stable, maintainable or robust solution. It worked for us because we were there to support it (and eventually rewrite it in .NET), but I wouldn't recommend it for anyone. I have, however, worked in government where trying to get anything done from "IT" (which I was part of) was so filled with red-tape and paperwork that departments would oftentimes just do the Access solutions.

Ultimately if that's the case you are in - where the departments simply can't get access to IT resources - then showing them at least some best practices for how to eventually scale the app would be helpful. As long as right after you show them, you put your resume out to find a better job.

Answer 4

I've had the misfortune to work on Access front ends like you describe, here are some non-Enterpise arguments.

Programming is easy! Creating forms in access is geared toward non-developers. Case in point, if you have multiple columns in a drop down, do you have list fields and data fields. No way! you just set he width of things you don't want to see to 0". So your looking at forms either thrown together by non-developers, or that will irk most people that have to work on them.

Versioning? Who needs versioning?, Just send out an attachment If changes need to be made to the front-end re-deployment is time consuming and fault prone.

This form, I'm thinking magenta The front end doesn't lock down well so end-users can get creative.

Answer 5

With Microsoft "giving away" free versions (MSDE, or SQL Express for 2005 onwards) of the SQL Server engine with each release, there is really no need to use Access any more. Although these free versions don't have a visual front end which can make development harder, good knowledge of SQL is all you need.

Answer 6

For a lot of CRUD (Create Read Update Delete) work, MS Access is OK. I'm more confident in it if the data is in another engine (MSSQL/Oracle/MySQL). However, most of the time I have problems with an MS Access database it's because:

1. It was home grown by a desktop user (not a programmer/IT professional) and hasn't planned ahead for future development (so additions are often more painful that if a pro had been involved)
2. It's full of unnormalized tables, inconstancies, and key-less tables.

My solution. Limit MS Access to the pro's and deploy the runtime version to the users desktops.

Answer 7

I started out doing desktop applications in Access with JET back-ends. I moved up to using SQL Server/MSDE with Access as the front-end and then VB6 and a smattering of classic ASP.

There are many "enterprisey" reasons to go with a "real" development tool like Visual Studio. For the scale you are inquiring about, thousands of users, I think those reasons may apply.

That said, I think there are scenarios where it still works to use Access. In my own experience, I fell back to Access with a SQL database when given a mandate to come up with an enterprise solution, albeit for a much smaller enterprise, in a very short period of time. The main reason driving my decision was time. I can put together a database UI in Access much, much faster than I can in any other tool. Some of that is familiarity with the tool, but a lot of it is that Access just gives you more database purpose-specific bits to work with out of the box. The Access UI can also be tweaked to look and operate very much like a standard WinForms app.

The hitch that many run into in an enterprise scenario is rolling Access and the application MDB/MDE out to the masses. This is easily resolved by setting it up on a Windows Terminal Server, which can also be rigged to operate almost like another app window on the client machine with the right RDP file parameters. But even that approach has its limits. I don't think it would scale into the thousands very well, but for several dozen users, I found that it worked just fine and bought enough time to meet the time constraints I had to work with so a web interface could be implemented when time allowed.

For a professional who knows what they're doing in a SQL database, an Access front end is not necessarily an unpardonable sin, especially when the mandate is cheap and fast and there isn't a religious purism involved.

Answer 8

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 9

I know quite a few professional Access developers who have developed and maintained Enterprise-level apps using Access as the front end (either MDB or ADP) and supporting user populations in the 100s (and even in a few cases, thousands).

Like any Enterprise-level application development, it requires a higher level of programming skill than building a little Access database for your 5-person department.

Oddly enough, the design principles that make for an efficient Enterprise-level app also make for a more efficient workgroup-level Access app.

I think the reason most of the people posting in this thread can't conceive of it as a good solution is simply because they've never seen it done properly, or were themselves not sympathetic to the development model that Access uses.

Yes, it's hard to do properly.

But at that level, so is every other development platform -- all of them require planning, experience and a high-level skillset.

And you can rag on Access apps developed by people without all of that (Enterprise or not), but frankly, I've encountered a boatload of non-Access database apps of all kinds that are incredibly badly implemented.

Sturgeon's law applies everywhere, and there's no reason to assume that Access development would be any different.

Answer 10

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.