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.)

Outcast

This is like asking are there cases where a bicycle is a better choice than a car. Sure there are. Bicycles are normally for recreation, cars are for serious transportation. Access vs. SQL Server is the same. – Heraldry

Agree with Randy that clarification is needed on what is being asked. You can't create a report in SQL Server! On the other hand you can create a SQL Server -based application in Access. My assumption is that the question being posed is really about whether to use Jet/ACE or SQL Server. Is that correct? – Clevis

@marc_s - thanks for the spelling edits. As a dyed in the wool Bluenoser myself (born and raised in Halifax, living on Long Island, NY, USA), I couldn't help but notice your avatar icon...care to explain? – Outcast

@Tom Bushell: <hehe> that flag does get noticed every once in a while. Well: I'm Swiss, but lived and worked in Halifax for three years (2000-2002) and made Nova Scotia my second home, so to speak. – Nationalist

@Randy Minder: The basic point is correct, but your terminology trivializes Jet/ACE as a database engine. – Simms

@Tom Bushell: please edit your question to use Jet/ACE where you say Access. Otherwise, it's pretty unclear what you're talking about. – Simms

@Nationalist - thanks for the explanation - hope you had a pleasant stay! – Outcast

@Tom Bushell: Regarding the "app dev" features. What do you want to compare them to? Obviously not SQL Server. And anyway Access applications can work perfectly well with SQL Server databases. – Clevis

@dportas: SQL Server has Reporting Services, which is also free. I don't know if it's available on Compact Edition, but it is on Express Edition (also free). – Fling

What SQL Server "app dev" features would you be comparing Access to? Specifically what parts of SQL Server provide the tools to create front ends without outside help? – Simms

@David-W-Fenton, BIDS - Business Inteligence Development Studio is part of SQL Server distribution/setup and is essentially Visual Studio .NET. Might be, I do not understand your question or what you are trying to convey (implicate) – Vaccaro

BIDS is part of SQL Server Express? Funny, it wasn't in there when I downloaded the most recent version last month, nor when I downloaded it from MSDN just after the release of 2008 R2. The point is, that's a separate product designed for use with SQL Server, not part of SQL Server itself. You may quibble over that, but it's a lot easier to have an integrated environment (try cobbling together what you need to really run a SQL Server Express installation compared to full SQL Server, for example). – Simms

Would benefit everyone if the Access as an IDE was separate from Jet/ACE v SQL Server. – Piazza

You can't decouple Access from Jet/ACE. Well, you can -- it's called an ADP, and didn't turn out to be very successful, and isn't very versatile. The reason Access non-ADP's are so versatile is precisely because Jet/ACE can connect to just about anything. Decoupling Access from Jet/ACE would completely remove 90% of the benefit of Access, seems to me. – Simms

Answer 1

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?

Simms

"you meant Jet/ACE, not Access". No I didn't - see my edit. But the point did need to be clarified, as you suggested. – Outcast

SQL Server Compact does not require a database install and is a single file with much better security, size capability, and a perfect candidate to synchronize with full SQL Server versions (regional office scenario). – Piazza

"Does not require a database install" -- what does that mean? Why is this comment attached to my answer, which doesn't say a damned thing about the Compact Edition of SQL Server? – Simms

OK, doesn't require a server database installation which is over-kill in most situations. Do you even have any arguements over the Compact Edition? You should. – Piazza

It has to be installed, which if your users are using Access, they don't have to install anything to share an MDB/ACCDB data file. It's also a case like Outlook and Outlook Express where it has a name that's very similar to a product with which it shares very little in common. SQL Server CE derives not from SQL Server but from the database with that name that was created for the predecessor to Windows Mobile, WinCE. So, I think the advantage of it are vastly overrated by those who think of it as a variant of SQL Server, as opposed to a completely different database engine. – Simms

...in any event, I don't know why I have an obligation to comment on Compact Edition. The question is not about CE, but about Access, so I see no reason why I personally should address a different database engine that I've never used (and never plan to use). – Simms

While the answer technically answers the question I think there is no scenario in which standalone Access engine (Jet) would be a superior solution - there are simply a dozen better non-server alternatives: sqlite, firebird, SQL Server Compact, just to name a few. Your answer seems to imply Jet is an adequate technical solution - it really isn't. Hence the downvote. – Stringhalt

Answer 2

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.

Fling

+1 . Access is a data-integrity nightmare..... have more than 2, 3 people edit the same "database" simultaneoulsy, and data corruption is bound to happen. This alone would be a killer criteria against ever using Access in my opinion – Nationalist

Hmmm, I thought Access did some kind of record locking (though as I recall it was very coarse grained). – Outcast

There is nothing wrong with multi-user Jet/ACE. If you're having problems, you just don't know what you're doing. – Simms

marc_s. You are wrong. I have a number of clients with 10 and 25 users in the database. – Kurr

@David-W-Fenton: The very fact that you "need to know what you're doing" to avoid things like data corruption, etc when using Access as a backend pretty much seals it's fate. I don't have to worry about whether adding more users is going to corrupt my sql server install. – Swop

You need to know what you're doing to keep any database engine running smoothly. Adding users to an Access/Jet/ACE is not going to corrupt your data any more than having sex will infect you with syphilis. It's only if there's something already wrong that you're running a risk. – Simms

@Simms - "running smoothly" is worlds apart from "data corruption." If the database is not runing smoothly, you don't lose data. Adding multi-user traffic to an Access database increases the odds of corruption. That it is even possible to corrupt the database (if not common since there is a tool in the application to repair a corrupted database) IMO rules out Access for anything where you care about not losing the data. – Pineal

My experience is that multi-user Access apps with Jet/ACE back ends don't corrupt. Oh, occasionally one gets flagged as suspect, but there's no actual corruption, just a dirty shutdown flag. – Simms

Until last month, I'd not seen any loss of data from a "corrupt" Access database in over 5 years. Last month, a client "lost" one record because of a power outage that took down all the workstations at once. After recovering the data (nothing was actually lost, since they could get all the information that was missing in that one record from paper documents). Since this was due to bad wiring in the building, I upsized them to SQL Server Express. Had they been in a building with modern wiring, I wouldn't have bothered with that (the problem would never have occurred!). – Simms

Answer 3

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

Off

It's only one examples. Not all Access apps will gain in performance when the back end is upsized, unless you revamp the front end to take advantage of the server. Most apps will exhibit an unpredictable mix of performance improvements and performance disasters. I have never been good at forecasting which will be which, as the expected performance drags often fail to appear, and expected performance improvements never materialize. You don't know until you try it, and even then, you really won't know for certain until it's in a production environment (unfortunately). – Simms

Answer 4

I have no wish to touch access as a development tool ever again. Horrid (partly because I came to it from VB but mostly because it horrid). Even with the front end (application) and and back end (just data) properly split it was a pain and a pig to version control (possible, but deeply unpleasant). Of course it may all be fixed in the latest incarnations... but I have no intention of finding out when one can do front ends so much better with other tools.

On the other hand

Jet/ACE makes a pretty decent data store for various classes of application

• No separate install onto an adequately current windows box (XP and better)
• Its easy to create (I cheat a bit in .NET to avoid a tonne of interop stuff) and maintain a database from code
• Its a single file - that makes it easy to back up and to take a copy of the database for use elsewhere
• Its stuffed full of proper relational goodness (-:
• Assuming a copy of access, trivial to look at the content of a data file
• For a single user desktop app it is (or at least was) ideal
• Works pretty well for small web applications (yes, it does - I've run systems with half a dozen or so users working on a content editor quite happily) and doesn't require anything more than a .NET enabled hosting account.
• And yes, if you want a shared database with no server it will do the job - doesn't want to be a lot of users or a lot of load but it works.

So... would I use it now? Well no, not any more - the world has moved on and better alternatives now exist - no more compact and repair.

• For a single user desktop app I would use SQL CE - much the same thing in many respects and I can in theory easily migrate to a server if it becomes a multi-user desktop app.
• For a web app I can now (now ish?) use SQL CE v4 which will support concurrent sessions which was not the case for previous versions. And I can migrate that to a server based version if/when I need to.
• For a shared database? SQL Server Express and if you don't want a server I don't really want the job thankyou.

Yes, .NET programmer (-:

Glyph

Answer 5

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.

Piazza

What maintenance tools (backup, optimization, etc.) does SQL Server Compact provide? – Simms

SSMS (just like all other versions of SQL Server), Visual Studio, has a designer for sync services in ASP.NET, has an API for full database control. use a file copy to back it up/restore, Automatic CAB Deployment for Windows Mobile devices. – Piazza

Have you installed a default SQL Server Express lately? While it includes SSMS, it doesn't include VS, nor a whole host of other tools (like SQL Profiler and Backup Agent) that come with full SQL Server, and I would argue are essential for management, maintenance and troubleshooting. – Simms

Why would anyone distribute an application where the user needs profiler? And besides, Express is not the same as the Compact edition which doesn't require the Backup Agent. Compact Edition is 10 times easier to manage for the user of a third-party application which was my whole point in the first place. – Piazza

"Why would anyone distribute an app where the user needs profiler?" Huh? I installed SQL Server Express on a client's server last month, and I needed a Profiler for troubleshooting performance issues. – Simms

To be fair, I went off the rails switching to a question about SQLExpress when my original question was about Compact. – Simms

@Simms - as a developer, can't you have Query Analyzer installed on your computer, connect to a client's SQL Server Express installation? How do you analyze queries in Access with only the runtime installed on a client's computer? The user doesn't need it. – Piazza

@Jeff O: in your question you seem to be assuming I'm carrying a laptop onto the customer site, which is not a valid assumption. Indeed, most of my work on that site (as with all my other SQL Server installations) is done via remote access. – Simms

@Simms - agreed, which is a huge benefit of having Access when you take over an application, most clients will have it installed unlike a .NET app that is a copy of a bunch of code files created with God know what version. – Piazza

I could certainly install my MSDN-licensed copy of Access on the server, since I would be using it for development purposes rather than production use. But that doesn't help with analyzing the communication between Access and SQL Server, which needs a profiler (there are free ones out there, e.g., sites.google.com/site/sqlprofiler) to trace what's going on. – Simms

SSMS is free to use. It just happens to be packaged with the SQL Server installation. – Piazza

Answer 6

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.

Horn

For your situation I would prefer SQL Server Compact. microsoft.com/sqlserver/2008/en/us/compact.aspx – Winni

@RedFilter: Cool. Didn't know that even exist. Does it require a local installation? Is it free? Thanks for the link! Reading it... – Horn

@RedFilter: Nevermind. Just read the link you sent. Pretty awesome! – Horn

SS Express Edition is free--cheaper than Access. Compact Edition is more on par with Access for functionality--no analytics, etc. – Fling

Cheaper than Access, perhaps, but not cheaper than Jet, which ships with all versions of Windows starting with 2000. It's also pre-installed, so you don't have to much about with distribution issues. – Simms

Considering you STILL have to install the latest MDAC to get the updated Jet, bug fixes, etc... then you still have to worry about distribution / installation. – Swop

You don't need the MDAC if you're going to use DAO. – Simms

Answer 7

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.

Clevis

I think your point is a valid one - like a lot of people, I tend to conflate the app development, reporting, and DB components into one thing called "Access". But the quality of the app dev, reporting, etc components might be a reason to pick one over the other - it would be interesting to hear some comparisons there too. – Outcast

I disagree that Access is development tool for independent from MSOffice products. See my #3823814. Your definition of file-sharing is also contradictory from my p.o.v. to me and have rather orthogonal relation to database management and development tools. – Vaccaro

Answer 8

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.

Onomastics

Answer 9

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.

Swop

Answer 10

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

Innoxious

Your first point is entirely irrelevant given that starting with A2007, the Access runtime is free. – Simms

Was somebody recommending using Jet/ACE as the database behind a web app? – Simms

@David-W-Fenton. thanks, I did not know, I am retarded dwelling on 2003. Though, 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). I do not know who recommends but it is more than frequent to see MsAccess used as backoffice (server) – Vaccaro

There are two important classes of users of Access, people who use it interactively, and people who don't give a rat's ass about Access, but use applications that run inside Access. These latter users are very well-served by the runtime. And in my opinion, they vastly outnumber the other users (when you throw out casual users who don't use Access interactively more every month or so). – Simms

Answer 11

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".

Scotopia

I've read until the "that was dwarfed by the information that was absent" and my hand jumped and upvoted your answer all by itself – Vaccaro

Compact and repair should be part of regular maintenance, just like you optimize and shrink as part of your SQL Server maintenance plan. If you don't include regular backups, and compact and repair as part of your maintenance routine for your Jet/ACE databases, you're falling down on the job just as much as you would be if you ommitted a maintenance plan for a SQL Server (or any other server database). All databases require care and maintenance. Complaining that Jet/ACE does is pretty unfair, and perhaps says more about the complainer than about Jet/ACE itself. – Simms

Answer 12

Many engineering apps use a JET database for configuration info. Think of it as an ini file on steroids. I need linked tables, but single-user only, and each user wants their own portable mdb file. My main app is a plotting program where the info is different plot setups, each with multiple channels that contain info in sub-tables that must link to the current plot. But, I use commercial engineering software that also stores setups in an mdb file, so my approach is not unique. I know this approach confuses most IT professionals since not the normal business app for a database. Microsoft also seems clueless since they decided SQL Server is better for everything, and trying to mandate that. Currently, I am looking at SQL Compact since I want a single file solution with no PC setup required. JET is fine for me, but Microsoft makes it hard in 64-bit, and requires work-arounds to co-exist w/ Office 32-bit. Sorry if my needs are outside the comfort zone or professional developers.

Ibarra

The HUGE problem with SQL compact is no support for ODBC. So crystal reports, and a HUGE NUMBER of programs say like Access, or FoxPro, or any ODBC client thus cannot use SQL compact due to no ODBC support. At least JET/ACE has support for ODBC, so "many" applications or in fact any that supports ODBC can thus connect to a ACE database via ODBC, something not possible with SQl compact. The fact that ZERO comments over 5 years of this thread fails to mention no ODBC support suggest no one here actually used SQL compact in a business environment since MOST programs can't connect to SQL compact. – Organist