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?
Best Answer
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
Main practical disadvantages
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.