Access Concurrent Users Limitation Solution?

Question

Hi!

I'm aware that Access has a limit of 255 concurrent users (sadly still using 2010 by the way) and I've got some thories to get around this however would like to get some advice on whether I'm on to anything.

So I want to use a access db to display information to an end user, which is specific to them based on the system knowing their environ username and returned data stored in a table using a front/back end set up. I also want the user to be able to submit data into the table also. I know exactly how to do this, however I'm concerned with a limit on the number of users accessing the db at the same time.

So my question to the world is does anyone know the specifics around this 'concurrent users' limitation. For instance, is this a 255 limit on the number of transactions of data being exchanged between forms and tables (and anything else in between). Or is it an actual limit on how many can open the database at any given time.

If it were the former, then could some code to 'que' the data if it exceeded the 255 limit get around this issue?

If it were the later, then would a portencial work around be to add something to the open event on the database to create a copy of the database and save it in a local location for each and every user every time it was opened. The new copy would have linked tables the back end so data could still be exchanged. Does this solve the multiple users issue by each user have their own copy of the database?

Would like any advice/help available.

Thank you!

Answer 1

When an Access app is opened a locking file is created that keeps track of all users. This locking file performs several functions including record locking. The limit on concurrent users is in this file. So there can only be 255 users currently logged in at the same time. 

Since a multi-user Access app should be split between a local front end and a shared back end, it is only the back end where the number of logged in users is a concern. Frankly, you will probably encounter performance issues before you reach the 255 limit. It would be a rare Access app that would have 255 people logged in and working with data at the same time.

The solution, though, is to store your data in a different database engine then the native Access data store. SQL Server is an alternative.

Answer 2

Scott thank you so much, that was exactly what i wanted to know.

I have come to the same conclusion that a back end such as an sql sever would be ideal but not possible in my situation.

So I'm faced with one access database as the user interface or 'front end' and another acting as a back end, purely having tables to store data.

In theory i would have more than 255 people accessing the front end, which i was originally going to share, so everyone would be accessing it simultaneously and updating the back end when ever accessing the data.

So i think i know the answer to my question, but I'll ask just to be sure, will this work?

Would you there for have any recommendations on how to go about creating a new local copy each time the database needs to be accessed?

Thanks in advance!

Answer 3

Your front end is installed locally, on each user's PC. While you may only have one version of the front end, your users will not be sharing that copy, they will have their own copies.

There are several options for deploying updates. I use a commercial product Auto FE Updater. This is a powerful and polished utility that has been around for many years. It works great. However, there are many other alternatives, some free, a search on auto FE Updaters should find a number of them.

I'm wondering why you think there will be so many concurrent users? Generally people load an Access app, make their changes and close it. So why would there be a need to keep it open and active?