Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ "access Is Only Good For One To Two Users At Most"

Posted by: ngins Nov 17 2019, 01:54 AM

Over in the Reddit MSAccess subreddit, someone asked about how scalable Access is. While most of us responded that it's scalable, but within limits, someone posted the following:

"Access is intended to be a RDBMS solution for SoHo environments with only one or two users at most or as a stationary database tool for one user in an enterprise space."

This person's claim that Access is only good for one to two users at most "comes from years of expeience building and implementing databases across multiple platforms."

While I shared with him how completely absurd his claims about Access are, if anyone here would wish to chime in and share your real-world experiences in using Access as a back end with more than two users, feel free. Here's a link to the thread:


Posted by: nvogel Nov 17 2019, 05:10 AM

Despite the title it appears that the original question is not about Access. It seems to be about the capabilities of the ACE database format and its filesharing model. I think the different answers are coming at this from slightly different perspectives because of that ambiguity.

ACE is not up to modern industry standards for multi-user data access because it lacks features that people take for granted in every modern data platform: concurrency management; ACID transactions and transaction logging; role-based security; archive log backups; GB+ scale. There are laws and regulatory standards concerning these things that ACE cannot satisfy.

This particular request apparently may be for a public body and the public sector is often required to meet higher standards than other organisations. It is also for a school, so in the absence of information to the contrary perhaps we should conclude that the database may contain especially sensitive data related to minors.

Access is possibly a good choice for this application but I don't think ACE is. It seems right to encourage the OP to look for alternatives.

Posted by: ngins Nov 17 2019, 05:27 AM

Yes, I agree that Access would not be appropriate for the original poster's intended use, even a theoretical one, which is a public library. My only qualm was with the responder's comment, that Access is only good for one or two users in a multiuser environment, which is patently false. That was why I posted a link to the sub-thread, where the responder posted that comment, and not to the main thread.

I have no problem with telling the original poster that Access would be inappropriate for his use. I am only disputing the comment that made up the title of this topic.


Posted by: nvogel Nov 17 2019, 05:39 AM

You misunderstood my answer. Access quite possibly is appropriate. Why do you think otherwise? ACE however is not a good option. So substitute ACE for Access in the title of this topic and that is the point under discussion.

Good is a relative term. If you think that ACE is "good" for multi-user applications then I'd be interested to know what you are measuring it against. My opinion would be that ACE cannot fairly and reasonably be described as "good" for multi-user applications given that it doesn't meet some very basic standards and that every other option, including free-to-use alternatives is likely to serve you much better.

Posted by: ngins Nov 17 2019, 06:02 AM

We're discussing two separate topics at the same time here. For the sake of clarification, let's define our terms.

When we discuss "Access", we're talking about the Access program running an ACCDB file or aa ACCDE against a backend that could be anything, such as SQL Server or Oracle.

When we're talking about "ACE," apparently we're talking about using the Access database engine as a backend with Access as a front-end or something else as a front-end.

Now, if that's the way that you're using those terms, then I'll reply.

First, I agree with you that "Access" can be used as a front end with a different database engine such as SQL Server as a backend. And in that regard, Access would be appropriate for his application. So I agree.

But the discussion, in both the thread and the sub-thread was about using Access, or ACE, as a backend. This is where you and I seem to disagree.

While I agree that Access as a backend would not be appropriate for large-scale applications, or for critical applications such as government institutions, hospitals, libraries, or things where personal privacy is critical, I also disagree that with the responders point, that Access can only handle one or two users at most when used as a backend.

This is the main and only point that I'm making. That in a private company, such as a small business, which has, say 10 or 15 or 20 users, that Access as both the front end and the backend would be very appropriate, provided that, as I noted, there are no government restrictions on how the data is handled.

Posted by: nvogel Nov 17 2019, 07:24 AM

"Appropriate" is a bit more equivocal than "good" I would suggest. Private companies and small businesses of course ARE subject to government's regulations on how data is handled - at least in USA, Europe and many other places. Even in cases where there are no relevant laws and regulations there are moral and practical reasons to do the right thing with data and many people understandably will want to recommend the best solution that they know. People of course apply their own professional experience and standards when deciding what to recommend but ACE comes near the bottom of the list for many people in the technology industry just because the majority have used DBMS software that is more scalable, robust, feature-rich and easier to use. People do inevitably judge ACE on that basis and find it wanting. Just my 0.02.

Posted by: isladogs Nov 17 2019, 07:33 AM

To be fair the poster (Thadrea) wrote this:

There are lots of great things you can do in Access, but you're kidding yourself if you think it's appropriate to be the backend of a substantial multi user application, let alone an enterprise solution. That isn't what Access is designed for-- Microsoft has a different product for those use cases.

The important part here is BACKEND
I have developed a number of commercial databases for schools for many years.
These all have Access FEs with anything up to 200 users logging on (to their own copy) simultaneously. However we used SQL Server for the BE for scalability, stability & security.
Trying to run that setup with an Access BE would lead to the system moving at the speed of a slug, frequent crashes, corruption and loss of data.
There would also be issues with the data being far less secure and very difficult to be GDPR compliant. See

Posted by: ngins Nov 17 2019, 07:44 AM

So, nvogel, is it your position then that ACE should not be used in any business application at all? That Access as a front end is fine, but never as a back end in any business application? Is that your position?

Posted by: ngins Nov 17 2019, 07:51 AM


Yes, I agree with you. No one is saying that Access should be used as a back end with 200 users! That would be ludicrous! I would certainly use SQL Server there.

The only point I'm making (and again, this is the ONLY point I'm making, as this discussion seems to be taking off in directions other than the one that I intended), is that the responder said that Access is good as a backend for only up to one or two users. Did you agree with that statement? I disagree with it wholeheartedly.

I believe that Access can be used as a backend for up to about 15 or 20 users doing simultaneous edits . Even more users if they're not doing edits.

That is the point I'm making -- that Access can handle far more than "one or two simultaneous users" -- though I certainly wouldn't say that it can handle 200 users as a back end!

Posted by: nvogel Nov 17 2019, 08:03 AM

I can't tell people what they should do but I do make recommendations. I can't really think of a reason to recommend an ACE database in a commercial environment. In a world where the demands on data are ever growing and the possibilities are limitless why would you recommend ACE over, say, Postgres or SQL Server Express or a dozen other options which cost nothing and make it much easier to do many more things?

Posted by: ngins Nov 17 2019, 08:09 AM

Well, it's not my intent to have a debate over this. I was just curious about your position, that is all. But I would say that there are many advantages to using ACE as a backend with an Access front and . For one, you're using a native database which has advantages over having to connect through ODBC. And two, speed of development is much faster . Also, SQL express , if I'm not mistaken, only allows one user . Unless that's a different version of SQL Server that I'm thinking out . If a company has 10 to 20 users, I believe they have to buy a license for SQL Server.

Anyway, like I said , it's not my intent to have a debate over this. I was just responding to your question with a couple points, is all. Thanks for your feedback.

Posted by: cheekybuddha Nov 17 2019, 08:16 AM


The RAD aspect is one reason.

When was the last time you used Access' table/query builder to create your SQLServer/other RDBMS' tables and queries?

The learning curve gets steeper by a factor of probably more than 2 when you add a server-based BE.

Also, even power users (who might be the creators of Access DB's) are rarely given access to maintain db's on a company server, so it often involves the IT Dept which makes life more tricky.

[Disclaimer: I would not generally recommend Access either - just trying to answer your question]


Posted by: ngins Nov 17 2019, 08:24 AM

Let's not forget the problem with heterogeneous joins when using a non-ACE back end, resulting in the inability to use front end temporary tables, which are very handy when running reports or doing extensive calculations.

Posted by: isladogs Nov 17 2019, 08:33 AM


the responder said that Access is good as a backend for only up to one or two users. Did you agree with that statement? I disagree with it wholeheartedly.

I believe that Access can be used as a backend for up to about 15 or 20 users doing simultaneous edits . Even more users if they're not doing edits.

That is the point I'm making -- that Access can handle far more than "one or two simultaneous users" -- though I certainly wouldn't say that it can handle 200 users as a back end!

These days I tend to have apps that only have a handful of simultaneous users or a large number like 200.
In the past I've also suggested 15-20 simultaneous users as being OK. Its hard to give a precise answer as there are a lot of factors to consider.

However when I've run training sessions with around that number of staff using a specially created Access BE , there have been some definite performance issues.
But then, of course, the trainees are often accessing the same parts of the app together (though not the same records). That may not be the case in the 'real world'

Posted by: ngins Nov 17 2019, 08:40 AM

Agreed. There definitely isn't a precise number. But definitely more than "one or two"! :-)

Posted by: nvogel Nov 17 2019, 09:24 AM

Hi David,

I'd say that using a different development tool is likely to make database development more rapid, not less. The Access table designer and query builder are surely incredibly inconvenient and limited compared to say, SQL Server Management Studio or even DBeaver. You are right, I do avoid the Access UI for database development tasks. Even if you do want to create an ACE database, DBeaver is arguably a better dev tool than Access. The Access UI has fine features for application development, but for database development the user experience is pretty clunky and includes some maddening bugs/"features" that are decades old.

Naturally any database development project would/should involve IT staff. All IT teams I work with are more supportive of creating databases on client-server DBMSs than they would be of ACE-based ones because ACE carries a lot more operational risk / information risk. If your IT team somehow does make your project more difficult then that seems like a management problem rather than a technology one.

Posted by: AlbertKallal Nov 17 2019, 03:51 PM

You don't want to temp me do you?

Ahhhh - could not resist jumping in!!

You all have a great day, ok!
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: FrankRuperto Nov 17 2019, 06:51 PM

It's funny how negative aspects of Access begin surfacing right after MS pushed the Office update that broke Access.
There's more fallout that will follow as a consequence of said update. So far 3 of my customers emailed me saying they want to migrate away from Access, Office and Windows.
I am starting to see stories about the damage. Look at the comments in this and this other
I also expect to see more negative stories in big media.

Posted by: DanielPineault Nov 17 2019, 07:28 PM

I lost my biggest client due to the 16+month issue with the bug that caused the unrecognized database error. Since A2016/O365 it has been continuous bugs. Nothing new here, just more of the same. We are guinea pigs doing MS' QA. All my new projects have been moved away from Access (and I mean every last one). Where I used to promote Access and Office (actively selling their product to client so they could use my databases), I am know doing my best to steer customers away from Microsoft products. MS just doesn't get it! And not to be rude, but they don't care to get it.

They always talk about end of support... but where's the support now?! December 10th to release a fix. You have got to be kidding me!!! Businesses are paying 100s-1000s of $$$$ to deal with MS' screw-ups over and over.

Posted by: ngins Nov 17 2019, 11:26 PM

Over the years Microsoft has tried to kill Access, wanting people to migrate to SQL Server instead, but they couldn't. When these serious issues started arising with Access in Office 365 months ago, I jokingly wondered if it was Microsoft's latest attempt to kill Access. With this latest bug, which has wrecked havoc on a large scale, and for which Microsoft has promised a fix in a month rather than simply rolling back the update, I'm beginning to wonder if what I once thought of jokingly is actually true. Especially when you see the effect it's having on businesses' trust in Access as a product.

Posted by: BruceM Nov 18 2019, 07:52 AM

Something that I do not think has been mentioned in this thread is that ACE is suitable only across a wired LAN, at least for data entry/edit tasks. This is because of the architecture of the database engine. While the following may be technically imprecise, I think it is a decent summation. ACE creates a local instance of the database engine, where it processes data before writing the result to the back end. Any interruption in the connection to the back end, such as can occur across a WAN (including VPN) or wireless network, that occurs during the write operation can lead to corrupted data. SQL Server and other server based systems send the data to be processed to the server. If there is an interruption it "waits" for the rest of the data. When it has all arrived, the data are processed and written to disk on the same physical machine (or at least one physically connected to the server), which all but eliminates the chance of interruption during the write operation.

Something like a maintenance database for a specific location, where the computers are connected across a wireless LAN, is still a good candidate for ACE. A database used from remote locations is not. If there is a mix of local and remote use DBs you may as well use the same engine for all of them, IMHO.

I don't think the point was addressed, so I will add that SQL Server Express is not limited to one user. Having SQL Server express on your computer or on the server may be a good development tool, depending on how balky it is when you deal with IT.

One other point is that servers are more and more often located off site rather than being a physical machine on the premises. That is another situation where the local, wired limitation of ACE is problematic. In general, I believe that model has a limited future.

Posted by: kfield7 Nov 18 2019, 08:46 AM

I don't believe my own experience is nearly as comprehensive as any of the other posters on this thread, as db development is only one aspect of my job(s) that I use for creatively solving specific needs.
i.e., "database developer" is not my job title.
Consequently, my experience only involves Access with ACE or Jet, although, for many of the reasons stated, I have researched using other dbs.

That being said, I could "argue" either side of many of the points raised, but I'd like to focus on what I perceive as the two main points, number of users and security.

I have read, on this very site I believe (since I don't frequent many other Access sites), where developers of certain applications have run with as many as 200 simultaneous users, but most seem to agree on 15 to 20 as a practical limit. I've never had more than half dozen users, due to the scope of my dbs.
I think this really depends on the complexity of the business model, and how creatively the db is designed to limit simultaneous use of the same parts of the db.

A business should never use Access? Really? It depends on the purpose and scope of the db. As pointed out, Access is not good at non-wired installations (enter here Citrix Server and others).
However, that limitation is precisely what makes security relatively easy -- the security is entirely network based, rather than Access based (I despised the security implementation of the older Access versions), allowing only certain users to use it, and it's not done over WAN, wireless, VPN, etc.. Now, giving specific users different permissions would have to be Access-based. Again, scope is key.
In my applications, the db often replaces what multiple Excel spreadsheets would otherwise be accomplishing. How secure are those spreadsheets?

I saw Albert saying he was tempted to chime in (but held back) - makes me curious, but he could easily just link to one of the many excellent treatises he has already provided on this forum.

For many of the other side issues, there are workarounds for using Access. But those issues drift from the OP.

In short, yes Access (front and back end) can be implemented for many more than 2 users, the actual number depends on scope and design.
If Access is used in a network controlled environment, it can be secure enough (no, I don't know what the "rules" are for security, but it stands to reason that if security was good enough for any spreadsheets being replaced, it can certainly be good enough for Access).
So, yes, Access can be an excellent db environment for business use. (Enter Farmers Insurance commercial reference. smile.gif )