maximum number of connections to network accessed Access database

Question

If I have a Microsoft Access 2007 database on a network location, is there a limit to the number of client computers that can use the database? The client will not have Access installed and instead will be using the Access Runtime 2007.

Will the maximum number of connections be changed if I split the front end from the back end as opposed to everyone simply accessing the file directly over the network?

I am aware that the approach described here is not ideal. I would not have designed it this way, but I gotta support it. Part of knowing how much the current design can take is knowing how many concurrent connections it can handle. Any information is appreciated!

Answer 1

Jet/ACE has a hardwired limit of 255 connections.

But you'll never get close to that in real life.

How many users can share your data simultaneously depends on what they are doing. If you have lots of read-only users, you'll be able to support more simultaneous users than if everybody is adding/editing all the time.

For an app with mostly read-only and some editors, you could pretty easily get 100 simultaneous users, but the more editing users, the fewer you'll be able to manage without jumping through hoops.

To me, the practical limit is somewhere in the 15-25 range. That's the point at which I'd start the process of upsizing to a server back end. It's not that you can't do better than that, it's just that to support more simultaneous users than that, you start having to be very careful with how you interact with the data, and you might need to go to unbound data editing forms. Once you've done that, you've given up 75% of the advantage of an Access front end.

As to the "splitting" issue, it's not so much a matter of how many simultaneous users, it's just a matter of not working at all if you don't split. Any Access app with more than one user needs to be split. No exceptions.

That way there's one back-end data file on the server, shared by all, and all users have an individual copy of the front end application on their workstations, with linked tables pointing to the shared back end stored on the file server.

In regard to connections, don't worry about that. An Access app with a Jet/ACE back end is more an issue of users, as you'll use a single persistent connection. It's counterproductive to try to manage connections, opening and closing them, as it taxes the file sharing mechanisms entirely unnecessarily. That is, you don't really gain much of anything in capacity, but you end up using up a lot of CPU cycles and bandwidth for no useful purpose.

This is, of course, heretical advice in comparison to client/server programming practices, but an Access app with a Jet/ACE back end isn't client/server, so you shouldn't be worrying about that.

Answer 2

Performance is not the issue. The problem is Access doesn't like sharing the forms, reports and modules in the same MDB/ACCDB to multiple users. This can lead to corruptions and other bizarre problems in the Access database file. To fix these problems means everyone has to exit from the database. Also you the developer will not be able to work on those objects while the users are in the database. In addition I prefer that users get MDE/ACCDEs so they're not tempted to muck with things.

See "Splitting your app into a front end and back end Tips" for more details. Also see the free for basic use Auto FE Updater utility at to make the distribution of new FEs relatively painless.. The utility also supports Terminal Server/Citrix quite nicely.