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

Question

This question was inspired by what I asked almost a year ago - any-orms-that-work-with-ms-access-for-prototyping - which recently became active, but as a discussion about access and SQL Server.

There seem to be a lot of access haters, and the main rap seems to be that it doesn't scale well (although some people seem to have been able to get it working).

For those of you who have used both technologies, there are times when you use Access over SQL Server?

Why?

And how can you improve your chances of success?

For example, on a desktop application with one or a small number of users, is access the best choice?

Or, to do the reverse course when you avoid access from the exit?

Again, why?

Edit When I say “Access”, I would like to receive feedback on two things:

• Using only the database component (Jet / ACE)

• Using application development functions (reporting, scripts, etc.)

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

(Just for the record, I don’t have a dog in this fight - I am pleased with the SQLite user.)

Answer 1

I will answer the question that you wanted to ask, not the one that you posted (you meant Jet / ACE, not Access).

Yes, there are many environments where Jet / ACE is the right data warehouse. I would say that the main problem is how many users you will have. For any 15-20 users, Jet / ACE will work fine. The only circumstances in which he will not be - it is simple if you simply do not know what you are doing. You may not have a clue if:

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

• You are trying to transfer this single monolithic file among multiple users.

• you intelligently break the database application into the foreground (forms / reports, etc.) and vice versa (only for tables), but try to split the interface between multiple users.

All of these scenarios are recipes for failure, but this is not a Jet / ACE issue, but an idiot who never bothered to learn how to develop and distribute an Access application.

Another common characteristic of low-access applications is to have forms bound to full tables, and not to selected subsets of records. Basically, you are developing your application to get the minimum amount of data at a time to allow the user to do its work. For example, the user editing one entry does not need the other 10,000 entries that are loaded per form.

All that said, the Jet / ACE rear-end Access application can work well with more than 15/20 users if these users are not in heavy data entry / editing mode. If users are mostly read-only, it's pretty easy to support up to 50 users.

However, if I were in this situation, I would most likely begin to insist on switching to SQL Server. But it should be noted that SQL Server adds significant administrative overhead compared to a simple file on the back panel. It’s easier to automate these tasks with full SQL Server than with SQL Server Express, so the recommendation for working with SQL Server Express is not very good for those who still do not have time to write and plan their own SQLCmd scripts.

Security can also be more complex. This is due to the fact that there is much more to SQL Server security, but you still need to use the interface when moving to it.

In an environment where there is expertise, you can use the number of users as your only reference to determine when to increase size. In small offices that lack experience and infrastructure, it is often best to use resources to stay with Jet / ACE for as long as possible.

For what it's worth, I have a dozen and a half active clients with Access applications, and only two currently work with SQL Server. Of the remaining ones, only two of them are even candidates, and there are simply not many good reasons for increasing them, since they are small users, they have no problems with performance or reliability, and there are no significant security problems.

In fact, several other points arise:

SQL Server may be better suited even for a single-user application if one or more of these issues is important:

• data is sensitive and must be protected beyond what is possible with Jet / ACE. Basically, if you need data that is protected beyond what you could do with an Excel spreadsheet, you need a database server engine.

• some applications reduce so much data that they really benefit from the server database engine, both in capacity and in the ability to transfer database operations to a completely different processor.

• some applications must be available 24/7, and downtime or the risk of losing even 1 byte of data is acceptable. In this case, it is recommended to use a server database.

In my experience, most people greatly overestimate their needs for all three of them and underestimate the ability of Jet / ACE to process data and maintain reliability.

EDIT: a script that for me is compelling for Access.

Let's say you have an office for 3 people without a file server, only 3 computers. You would:

• tell them to buy a standalone server by providing it as SQL Server (and possibly also a file server for them), and then keep that in mind.

• install SQL Server on one peer workstation and ask them to use their application for this.

• just use Access.

The first two cases require a lot of additional maintenance and administration (although your Jet / ACE also requires maintenance). Who will do this?

If you choose # 1, where will the money be for this server, and labor will configure it, and labor will support and administer it over time?

If you choose # 2, what if there is no workstation that is sufficiently equipped to run both SQL Server and the workstation?

Answer 2

Are there any cases of using Access over SQL Server?

When I was not going to be the one who supported him.

Access is more common and less involved in administration than SQL Server. The likelihood that a client will find someone who can support the setup would be better, and it would cost less to get trained (most postgraduate institutions and recreation centers have classes).

on a desktop application that will have one or a small number of users, could Access be a better choice?

As far as I know, Access is still not a good choice when two or more people can update the same record at about the same time. And in light of the free options like SQL Server Express or Compact Editions, PostgreSQL or MySQL, this is a tax for end users who need control (although they probably shouldn't have it for denormalized data).

when should you avoid access from go go?

When the importance of data is recognized, as well as the impact of data migration.

In addition to the free version, I recommend SQL Server Express Edition because you can move editions as notes. Similarly for Oracle Express Edition. PostgreSQL will be my next recommendation after any of the above; MySQL lags behind developer functionality and unlike PostgreSQL, a commercial license may be required.

Answer 3

As others have said, there are clear cases where access does not correspond to the assignment, and some, where it is normal, I would like to share with you the question of the border with my work.

I made a fairly wide range of applications that work together and provide many features, such as quality checks, tracking of written procedures, error logging, shift planning, and call center operations. All applications have separate backends, but some of them become quite large.

Now in my work I support two sites each on separate networks and with different IT departments that control them. On the side that I work on the most, we managed to push the old Dell server to start SQL Server 2008R2, life was good, and I started a project to increase these applications (which are not all attached) to the SQL server.

Time passes, and we release new versions from the side of SQL, users are satisfied, as the processing time decreases from about 2 seconds to 0.3 seconds. Also with extra power, I can start adding new features.

The other side, however, does not even use the concept of SQL Server (even virtual), working with a database that was not developed in the ivory tower of IT. So they are stuck in an older version of access, which still works just fine, only an honest piece is slower.

This is almost an ideal situation with A / B testing, applications are on the boundary line "need" improvement and the benefits are proven, but access still works fine. With careful coding and fairly good access to the file server, you can do amazing things.

As another note on the side of the SQL server, I had to configure other separate applications, since it was used only by 5 or so people that I kept in access / street and did not touch the SQL server, its all about using the right tool for working . I often think of these IT people who say that access is only suitable for one user and each time clicks on the server server, as the type of people who carry around a sledgehammer in case they need to open several walnuts.

Answer 4

I have no desire to access access again as a development tool. Horrid (partly because I came to him from VB, but mainly because of his horror). Even if the front part (application) and the back part (only data) were correctly separated, it was a pain and a pig for version control (perhaps, but very unpleasant). Of course, all this can be fixed in recent incarnations ... but I'm not going to find out when you can make the front ends much better with other tools.

On the other hand

Jet / ACE makes a pretty decent data warehouse for various classes of applications

• No separate installation on an adequately functioning window window (XP and better)
• It's easy to create (I cheat a bit in .NET to avoid tons of interop stuff) and maintain the database from code
• Its single file - it makes it easy to back up and copy the database for use elsewhere
• His filled complete relational kindness (-:
• Assuming a copy of access is trivial to view the contents of a data file
• For a single-user desktop application, it (or at least was) ideal
• It works well for small web applications (yes, it is - I quite successfully started the system with half a dozen or so users working on a content editor) and does not require anything more than a .NET hosting account.
• And yes, if you want a shared database without a server to do this job, you don’t want a lot of users or a lot of work, but it works.

So ... would I use it now? Well no, no more - the world has moved, and now there are better alternatives - no more compact and repair.

• For a single-user desktop application, I would use SQL CE - almost the same thing in many ways, and I can theoretically easily go to the server if it becomes a multi-user desktop application.
• For a web application, I can now (now ish?) Use SQL CE v4, which will support concurrent sessions that were not the case with previous versions. And I can port this to the server version if / when I need.
• For a shared database? SQL Server Express, and if you do not want a server, I really do not want the work to be thankful.

Yes, the .NET programmer (-:

Answer 5

Jet / ace

• When security is not critical.
• Data size can be saved up to several hundred mega.
• Installation and maintenance of SQL Server is too much for the user; although the compact version does not need this
• You prefer to build an external interface using Access as, and sometimes using other data sources may have limitations.

Access

• The intended functionality is sufficient / not recommended to reinvent the wheel.
• One of the easiest and most flexible reporting tools if you do not have a VBA bias.
• It is easier to allow authorized users to have custom queries and custom reporting capabilities.
• Easier to integrate with other Office products.
• It's easier to switch from Excel (Bash Access to all of you, but when Excel is used as primary db ...).
• If you are a consultant working on the site using a special application, Access is an excellent choice. Visually, Studio One Click greatly simplifies the distribution and updating of applications in bulk.

Although my criteria may seem narrow in scope, I have found that most business needs are being processed. You are lucky if you stay in business, not to mention the transition Access. When most companies need to scale outside of Access (the development team has also grown.), Their business rules tend to change so dramatically that you will rewrite many applications anyway, but the original Access application has developed many of the requirements.

Answer 6

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

• I can not afford the price of an MSSQLServer license.
• The program that I run is a standalone program running on a low power device (i.e. NETBOOK)
• The database is not , which is important for my application, I just want a small repository for some additional information.
• I do not want the user to install SQLServer on his machine just to run my application.

Answer 7

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

However, you might be asking if there is any reason to choose a database architecture for file sharing (e.g. Jet / ACE) instead of a client-server (e.g. SQL Server). In my opinion, there are no good technical reasons, except in extreme cases of using very simple single-user desktop applications. Client-server far surpasses TCO, scalability, manageability, availability, security and much more. For a number of other reasons, the client-server DBMS is better suited to the needs of most corporate environments.

Perhaps the main reason people chose the file sharing model these days is probably not because it has some inherent advantages. Most likely, this will be because they have an existing database of applications and people already working in this way, and they have no incentive to make changes.

Answer 8

I recommend Access for small, single-purpose, single-user needs. In my case, I use Access to retrieve data from multiple SQL servers, query data, sort data, and finally send email.

This is not ideal, of course, but there really is an incompetent software.

Answer 9

I cannot imagine in what situation I would never want to deploy an access-based application.

There is no Access feature that I have ever said about: "Wow, I wish it were!"

However, on the other hand, about once a year my name is to pick up the pieces after some joker assembled the Access application for the company that tanked. Usually this happens at the most inopportune time for them.

The fact is that the applications that we create tend to stick for quite some time. Everything that you build, most likely, will last long after you move. And at some point, these applications are so ingrained in the life of the company that they are really critical. Especially for small companies that lack the resources to support full-time developers to constantly innovate.

So, after 5 years, the company working with this small Access application is likely to grow. The question you ask yourself is whether you are positioning them for growth or are you deliberately hindering this growth by choosing a technology that cannot receive them?

Given that you cannot simply switch Access for backup to the SQL server without time, the last route ensures that they have to spend significant resources on replacing it.

Some developers may not care; I do.

Answer 10

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

It is not possible to create a web application intended for public access from the Internet, because in this case Webapp can be used only after verifying that the user has acquired the MS Access license.

In addition, MS Access is not intended for automatic (non-user) processing [1]:

• Microsoft does not recommend or support the automation of Microsoft Office applications from any defenseless, 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 running in environment "[1]

Plz check the listing of problems in the "Problems Using Office Server Automation" section [1]

Update
David-U-Fenton noted that the execution time of MsAccess2007 is free. I believe that most people use (and want) MSAccess, primarily because they are used to Access as a client (GUI), and not because of the server part (runtime).

So, summarize:
The initial MsAccess environment has become free (although most ppl use pre-2007 Access and do not see any good reason for updating), but it does not make sense to automatically participate, that is, when MSAccess-based products are intended for internal use (not for sale and distribution) and have already been purchased anyway.

And MsAccess, since the / GUI / client interface, when it makes sense to distribute and sell MsAccess-based software products, is not free.

Isn't that a catch-22 problem (catch-33?)?

MsAccess (MsOffice) is not an AD / IDE (tool or platform developer) for developing independent software products, and it does not have free releases. I would say that he does not develop the platform at all. The development tools and functions of VBA, MsOffice are intended only to support the functions of the end user MS (MS Office). You cannot reuse them without a license / permission from the MS provider. Their insides and specifications are not in the public domain - semi-documented, changed without notice, cannot be redone / open or re-implemented.

You cannot develop a product based on MSAccess, hoping to distribute, sell, show free service, even be supported by the client without buying the first MsAccess license.

Update2
Back in 2002, I was developing a carefree backoffice application. It was required to support both Access97 and Access2000. I encountered a serious error in the Jet driver, and when I reported this to MS, the answer was that it is no longer supported. It was a deadlock - an undocumented, closed "platform" with errors and unpredictable unstable behavior + unsupported more.
I believe this is the same risk for any recent MsAccess-es obligations.

[one]
Considerations for Automated Server-Side Automation
http://support.microsoft.com/kb/257757

Answer 11

I used to think that ACE / Jet was a neat little small SQL product. Although I never thought it was almost as good as SQL Server, there were a few things that ACE / Jet can do that SQL Server cannot: [think for a few moments ...] CHECK restrictions that support subqueries and FK for example, with several cascading tracks. Such things that are useful when prototyping without the need for a workaround.

I never chose to use ACE / Jet in production. The systems that I supported, which I really used, always suffered from complex problems that almost always went away when I manually made "compact and repair", which indicates a fundamental drawback of this technology. Oh, yes, of course, we made some terrible mistake, but we were all competent SQL programmers and software developers, and we did not seem to do anything wrong (we, of course, were not interested in investing in an Access specialist). I have heard of hundreds of similar events.

One of the serious issues that I have always experienced is the lack of good documentation from Microsoft about ACE / Jet. The last time I looked (Access2007), Access Access contained SQL syntax that did not exist and never existed in the product, but it was overshadowed by missing information. I have my favorite options that I could tell you, but take something simple and fundamental, such as data type precedence or decimal rounding, and you will find it vain to search the documentation for such rules. In SQL Server, most of the basic rules can be found in the SQL-92 Standard specification, but unfortunately, ACE / Jet has never been and never will be compatible with SQL-92.

I no longer use ACE / Jet. What SQL Server can do using SQL is far superior to what ACE / Jet can do - that I can no longer do without considering SQL-92 compliance, common table expressions, stored procedures with multiple statements, DATE type data, window dialing features, OUTPUT clause, ... so many sources instantly to mind!

SQL Server is a much more dynamic product than ACE / Jet. Being an end user, I (I feel) can participate in shaping my future: I can report errors and receive timely feedback from the development team; I can vote for bug fixes. SQL Server Help (BOL) is excellent and contains Community Content. By comparison, Jet hasn’t received new features for nearly a decade, then ACE came with many cool features ... for SharePoint! I cannot report ACE / Jet errors (wherever I start!), And there would be no hope of fixing them, because MS does not invest in ACE / Jet for end users. I can't even get them to fix glaring errors in Access help.

I would suggest only those cases where ACE / Jet is a better choice than SQL Server, when you have already invested in ACE / Jet, and you are not ready for a change. I was reminded of a quote from Jeremy Clarkson , "people-carriers for people who refused."

Answer 12

Many engineering applications use the JET database for configuration information. Think of it as an ini file on steroids. I need related tables, but only for one user, and each user wants to have their own portable mdb file. My main application is a charting program in which the information is a different chart setting, each of which has several channels that contain information in sub-tables that should refer to the current chart. But I use commercial engineering software, which also stores the settings in the mdb file, so my approach is not unique. I know that this approach is confusing for most IT professionals, as it is not a regular business database application. Microsoft also seems unlikely, as they decided that SQL Server is the best for everything and is trying to do it. I am currently looking at SQL Compact, as I want one solution for files without the need to configure a PC. JET is great for me, but Microsoft does it hard in 64-bit mode and requires collaboration to work with 32-bit Office. Sorry if my needs are outside the comfort zone or of professional developers.