Are there any cases where MS Access is a better choice than SQL Server?

Question

This question was inspired by one I asked almost a year ago - any-orms-that-work-with-ms-access-for-prototyping - which has recently become active again, but as an Access vs SQL Server debate.

There seem to be a lot of Access haters out there, and the main rap seems to be that it doesn't scale well (though some people seem to have been able to make it work).

For those of you who have used both technologies, are there cases where you would use Access over SQL Server?

Why?

And how can you improve your odds of success?

For example, on a desktop app that would have one, or a small number of users - might Access be a better choice?

Or, to take the reverse tack, when should you avoid Access from the get go?

Again, why?

Edit When I say "Access", I'd like to get feedback on two things:

1. Using just the database component (Jet/ACE)

2. Using the app development features as well (reporting, scripting, etc)

After all, there might be advantages to using some of the app dev features, if your application can live with the limitations of the DB side.

(Just for the record, I have no dog in this fight - I'm a satisfied SQLite user.)

Answer 1

I would use Access over SQLServer on any of these situations:

1. I can't afford MSSQLServer licence prices.
2. The program I will run is a standalone program running on a low power device (i.e. NETBOOK)
3. Database is not that important to my application, I just want a small repository for some secondary information.
4. I don't want the user to install SQLServer on his/her machine just for running my application.

Answer 2

Jet/Ace

1. When security is not critical.
2. Data size can be kept to a few hundred meg.
3. Installation and maintenance of SQL Server is too much for the user; although the Compact version doesn't need this
4. You prefer to build with Access as the front end and sometimes using other data sources can have limitations.

Access

1. Pre-built functionality is sufficient/ prefer not to reinvent the wheel
2. One of the easiest and most flexible reporting tools unless you have a VBA bias.
3. Easier to allow power users to have ad hoc query and custom reporting capabilities.
4. Easier to integrate with other Office products.
5. Easier to transition away from Excel (Bash Access all you want, but when Excel is used as a primary db...).
6. If you are a consultant working on site with a custom application, Access is a great choice. Visually Studio One Click makes distributing and updating applications in mass much easier.

Although my criteria may seem narrow in scope, I've found that most business needs are handled. You're lucky if you stay in business let alone outgrow Access. When most businesses need to scale beyond Access (Development team has grown as well.), their business rules tend to change so drastically you'll be rewriting many apps anyway, but the initial Access app will have worked out many of the requirements.

Answer 3

Are there cases where you would use Access over SQL Server?

When I wasn't going to be the one supporting it.

Access is common, and less involved for administration than SQL Server. The chance of the client finding someone who could maintain the setup were better, and it costs less to get training (most post-secondary institutions and recreation centres have classes).

on a desktop app that would have one, or a small number of users - might Access be a better choice?

To my knowledge, Access is still not a good choice where two or more people can be updating the same record around the same time. And in light of free options like SQL Server Express or Compact Editions, PostgreSQL or MySQL, it's a tax on end users who want control (though they likely really shouldn't have it, for sake of denormalized data).

when should you avoid Access from the get go?

When the importance of data is recognized, along with the impact of data migration.

Besides being free, I recommend SQL Server Express Edition because you can move up Editions as drop-in replacements. Likewise for Oracle Express Edition. PostgreSQL would be my next recommendation after either of the above; MySQL lags in developer functionality and unlike PostgreSQL--can require a commercial license.

Answer 4

Access is an application development tool. SQL Server is a DBMS. They are not the same thing!

However, maybe you are asking whether there any reasons to choose a file-sharing database architecture (like Jet/ACE) instead of a client-server one (like SQL Server). There are no good technical reasons in my opinion, except marginal cases of very trivial single-user desktop uses. Client-server is far superior in terms of TCO, scalability, manageability, availability, security and pretty much everything else. For a host of other reasons client-server DBMS is a better match for the needs of most corporate environments.

Perhaps the main reason why people would choose a file-sharing model these days is probably not because it has any inherent advantage. More likely it would be because they have an existing base of applications and people already working that way and haven't the incentive to make a change yet.

Answer 5

I recommend Access for smaller, single-purpose, single-user needs. In my case, I use Access to get data from a couple of SQL Servers, query the data, sort the data, and finally email it.

It's not perfect, of course, but no capable software really is.

Answer 6

I'll answer the question you meant to ask, not the one you actually posted (you meant Jet/ACE, not Access).

Yes, there are plenty of environments where Jet/ACE is a suitable data store. I would say the main issue is how many users you're going to have. For anything up to 15-20 users, Jet/ACE will work just fine. The only circumstances in which it won't is if you just don't have a clue what you're doing. You may not have a clue if:

1. you create a single monolithic MDB/ACCDB file with both tables and forms/reports etc.

2. you try to share that single monolithic file among multiple users.

3. you wisely split your database application into front end (forms/reports/etc.) and back end (tables only), but try to share the front end among multiple users.

All those scenarios are recipes for failure, but it's not Jet/ACE that's at fault, but the idiot who never bothered to learn how to design and distribute an Access application.

Another common characteristic of poor-performing Access apps is to have forms bound to full tables instead of to selected subsets of records. Basically, you design your app to retrieve the minimum amount of data at a time in order to allow the user to do her work. A user editing one record doesn't need the other 10000 records loaded behind the form, for instance.

All that said, an Access app with a Jet/ACE back end can still perform well with more than 15/20 users if those users are not in heavy data entry/editing mode. If there are mostly read-only users it's pretty easy to support up to 50 users.

However, were I in that situation, I'd likely start urging upsizing to SQL Server. But one needs to note that SQL Server adds significant administrative overhead in comparison to a simple file on the back end. It's easier to automate those tasks with full SQL Server than with SQL Server Express, too, so the recommendation of going with SQL Server Express is not a very good one for anyone who is not already comfortable with writing and scheduling their own SQLCmd scripts.

Security can also be more complicated. This is a consequence of there being a lot more you can do with SQL Server security, but it still has to be addressed on the front end when upsizing.

In an environment where administrative expertise is available, you can use number of users as your only benchmark for deciding when to upsize. In small offices that lack that expertise and infrastructure, it's very often a better use of resources to stay with Jet/ACE as long as possible.

For what it's worth, I have a dozen and a half active clients with Access apps and only two currently running against SQL Server. Of the remainder, only two of them are even candidates, and there simply aren't very many compelling reasons to upsize them, as they are small user populations and they have no performance or reliability issues, and no significant security concerns.

That actually raises a couple of other points:

SQL Server might be a better fit even for a single-user app if one or more of these issues is significant:

1. data is sensitive and needs to be secured beyond what's possible in Jet/ACE. Basically, if you need the data secured beyond what you could do with an Excel spreadsheet, you need a server-based database engine.

2. some applications crunch so much data that they really benefit from a server database engine, both in capacity and in the ability to hand off the database operations to a completely different CPU.

3. some applications need to be available 24/7 and no down time or any risk of the loss of even 1 byte of data is acceptable. In that case, a server-based database is advisable.

In my experience, most people vastly overestimate their needs for all three of these, and underestimate Jet/ACE's ability to process data and maintain reliability.

EDIT: A scenario that to me is compelling for Access.

Say you have a 3-person office with no file server, just 3 PCs. Would you:

1. tell them to buy a standalone server, provision it as a SQL Server (and perhaps as a file server for them, as well), and then have them use that.

2. install SQL Server on one peer-to-peer workstation and have them use their application running against that.

3. simply use Access.

In the first two cases, there's a lot more maintenance and administering that needs to be done (though there's maintenance required for your Jet/ACE back end, too). Who is going to do that?

If you choose #1, where is the money going to come for for that server and the labor to set it up and the labor to maintain and adminster it over time?

If you choose #2, what if there's no workstation that's sufficiently equipped to act as both SQL Server and workstation?

Answer 7

I can conceive of no situation in which I would ever willingly deploy an Access based application.

There is no feature of Access that I have ever said "Wow, I wish I had that!"

However, on the flip side, about once a year I get called to pick up the pieces after some joker put together an Access application for a company that tanked. Usually this happens at the most inopportune time for them.

The fact is, the apps we build have a tendency to stick around for quite some time. Whatever you build will more than likely still be there long after you've moved on. And, at some point, these apps become so ingrained in the company's life that they are truly mission critical. Especially for smaller companies which lack the resources to keep full time developer's around to constantly innovate.

So, 5 years from now, the company running that little Access app is probably going to grow. The question you have to ask yourself is whether you are positioning them for growth or will you purposely hamper that growth by picking a technology which can't get them there?

Considering that you can't just switch Access over to be backed by a SQL server without time investment, the latter route guarantees that they will have to spend significant resources in replacing it.

Some dev's might not care; I do.

Answer 8

As others have said there are clear cases where access is not up to the job and some where it is fine, I would like to share a border line case with you from my job.

I have made a rather spiffy set of applications that all work together and provide many functions such as quality reviews, written procedure tracking, breach logging, shift scheduling and call centre operations. The applications all have separate backends but some were getting quite big.

Now in my work I support two sites each on separate networks and with different IT departments controlling them. On the side I work on the most we managed to press an old Dell server into running SQL server 2008R2, life was good and I started the project to upsize these applications (which are all unbound) to SQL server.

Time passes and we release the new versions on the SQL side, users are happy as processing times drop from about 2 seconds to about 0.3 seconds. Also with the extra power I can start adding new features.

The other side however wont even entertain the notion of an SQL server box (even a virtual one) running a database that was not designed in the ivory tower of IT. So they are stuck on the older access version which still works just fine just a fair chunk slower.

This is an almost perfect A/B testing situation, the applications are on the border line of “needing” to be upsized and the benefits are proven but access is still working just fine. With careful coding and a good enough file server access can do surprising things.

As another side note on the side with the SQL server I had to setup another separate applications, as it was only being used by 5 or so people I stuck with doing it in access/jet and not touching the SQL server, its all about using the right tool for the job. I often think of these IT people who say access is fit for only one user and push for a server backend every time as the type of people who carry around a sledgehammer just in case they need to open a few walnuts

Answer 9

One cannot be sure that all potential customers have bought MS Access (MS Office). One can create free solutions based on SQL Sever but not on Access.

It is impossible to create webapplication intended for public access from internet since, in this case, webapp may be used only after verification that a user purchased MS Access license.

Besides, MS Access is not intended for unattended (non-user-interactive) processing [1]:

• Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment" [1]

Plz check the enumeration of problems in "Problems using server-side Automation of Office" section of [1]

Update
David-W-Fenton noted that MsAccess2007 runtime is free.
I beleive, most people use (and want) MSAccess, first of all, because they are accustomed to Access as client (GUI frontend) and not due to server part (runtime).

So, let's summarize:
MsAccess runtime started to be free (though most ppl use pre-2007 Access and do not see any compelling reasons to upgrade) but does not make sense for unattended engagement, i.e. when the MSAccess-based products are for internal use (not for sale and distribution) and already had anyway been bought.

And MsAccess, as frontend/GUI/client, when it make sense for distibution and selling of MsAccess-based software products, is not free.

Is not it catch-22 (catch-33?) situation?

MsAccess (MsOffice) is not AD/IDE (developing tool or platform) for developing independent software products and it has no free editions. I'd say it is not developing platform at all. VBA, MsOffice developing tools and features are intended as supporting features to MS end-user product (MS Office) only. One cannot re-use them without licensing/permission from their vendor MS. Their internals and specs are not in public domain - half-documented, changed without notice, cannot be redone/open-sourced/re-implemented.

One cannot develop MSAccess-based product hoping to distribute, sell, show free demoor even be maintained by customer without customer first buying MsAccess license.

Update2
Back in 2002, I was developing backoffice unattended aplication. The requirement was to support both Access97 and Access2000. I encountered serious bug in Jet driver and when I reported it to MS the answer was that it is not supported anymore. It was a dead-lock - undocumented closed "platform" with bugs and unpredictable unstable behavior + unsupported anymore.
I guess, it is the same risk for any more recent MsAccess-es engagements.

[1]
Considerations for server-side Automation of Office
http://support.microsoft.com/kb/257757

Answer 10

I used to think that ACE/Jet was a neat little neat little SQL product. Though I have never thought it was anything near as good as SQL Server, there have been a few things ACE/Jet can do that SQL Server cannot: [thinks for a few moments...] CHECK constraints that support subqueries and FKs with multiple cascade paths, for example. Such things that are useful when prototyping without having to implement a workaround.

I have never chosen to use ACE/Jet in production. The systems I supported that did use it always suffered from stange problems which almost always went away when manually doing a 'compact and repair', suggesting there was a fundamental flaw with the technology. Oh yes, no doubt we were committing some terrible mistake but we were all competent SQL coders and software engineering generalists, and we didn't seem to be doing anything wrong (we certainly had no interest in investing in an Access specialist). I have heard of hundreds of similar experiences.

One of the big problems I've always had is the lack of good documentation from Microsoft about ACE/Jet. Last time I looked (Access2007) the Access Help contained SQL syntax that did not and has never existed in the product but that was dwarfed by the information that was absent. I have my particular favourites I could bore you with but take something simple and fundamental like data type precedence or decimal rounding behaviour and you will find look in vain in the documentation for such rules. In SQL Server much of the basic rules can be found in the SQL-92 Standard spec but sadly ACE/Jet has never been and never will be SQL-92 compliant.

I no longer use ACE/Jet at all. The things that SQL Server can do using SQL now far, far outweigh the things ACE/Jet can do -- things I can no longer do without included SQL-92 compliance, common table expressions, multi-statement stored procedures, the DATE data type, windowed set functions, the OUTPUT clause, ...so much springs instantly to mind!

SQL Server is a much more dynamic product than ACE/Jet. As an end user, I (feel I) can get involved with shaping its future: I can report bugs and get timely feedback from the development team; I can vote for bug fixes. The SQL Server help (BOL) is excellent and contains Community Content. In comparison, Jet received no new features for almost a decade, then ACE came along with lots of cool features... for SharePoint!! I can't report ACE/Jet bugs (where would I start!) and there would be no hope of getting them fixed anyhow because MS isn't investing in ACE/Jet for end users. I can't even get them to correct blatant errors in the Access Help.

I would suggest the the only cases where ACE/Jet is a better choice than SQL Server is when you have already invested in ACE/Jet and you are not prepared to change. I'm reminded of the Jeremy Clarkson quote, "people carriers are for people who've given up".