7

What are the maximum concurrent connections ms-access supports?

ramu
  • 1,019
  • 3
  • 15
  • 41
  • 3
    When you need Access to stay consistent and reliable: just one connection. Access was not designed for concurrency. I’ve seen many Access database get correct because of this. – Steven Apr 13 '11 at 09:08
  • @Steven: s/correct/corrupt/ ? – Mat Apr 13 '11 at 09:30
  • @Mat: You are right about the typo. I mean "corrupt" not "correct". – Steven Apr 13 '11 at 09:39
  • 3
    Steven is wrong. Access database files are designed with concurrency in mind. I wouldn't use it for a very active website, with more then 10 or 20 users entering/updating or for mission critical data. – Tony Toews Apr 14 '11 at 03:32
  • 2
    @TonyToews: It's great that you haven't had problems, but other people have. Lack of evidence is not evidence of lack. My preferred solution is to split the front end and the back. Every user gets their own GUI .accdb, communicating with a single back end. IF you can't afford a real database. –  Aug 22 '13 at 20:41

3 Answers3

12

In theory 255, in practice well it’s depends. If they are mostly read only and the network you are on is fast then I have seen 100+ with no problems on a well written database. On the other hand I’m sure the same old horror stories of databases getting corrupt with 2 users will come up. Yes it can happen but if you make a good database and are realistic about what you do with it you can get a surprising number of users hanging off one.

In the long term you might want to look at the free (Express) version of SQL server for higher user counts.

Kevin Ross
  • 7,185
  • 2
  • 21
  • 27
0

Literally, "the Jet database engine versions 1.1, 2.0, and 2.5 can only be used by 10 client applications simultaneously on any one machine". Starting from Jet 3.0 there is no such limitation. See this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;154869

Pere
  • 1,068
  • 12
  • 20
  • That article was last reviewed in 2004, and explicitly refers to 16-bit versions of Jet. That *same* article also says that the limitation doesn't apply to Jet 3.0 (32-bit). – Mike Sherrill 'Cat Recall' Feb 10 '15 at 02:55
  • Why the downvote? The original question didn't specify the version he was asking for. Also, I quoted the passage of the article which states the versions affected. There is the link also for the user to check, as I cannot quote the entire text here. Anyway, I'm editing my answer, for the sake of clearnes, to add (in case it wasn't clear enough) that starting from v3 that limit doesn't exist, in case anyone missed the "1.1, 2.0, and 2.5" part. Hope you feel better now. – Pere Feb 10 '15 at 08:57
  • 1
    Unless the OP tells you differently, it's pretty safe to assume they're not interested in the limitations of 16-bit software, or in the limitations of software released in the previous century. – Mike Sherrill 'Cat Recall' Feb 10 '15 at 13:37
  • One can make a lot of assumptions, and the other the opposite ones. So if you don't like an advice, to me is more fair to simply ignore it, as long as it isn't based on false claims and it's not against any written guidelines. In my case, I linked to a MS database support article that is *used* (not directly cited) in the article referred by the accepted answer - although that article in @Mat answer is merely an opinion, not actually a reference. The 2nd answer doesn't even cares citing any references, and still it's not voted negatively. I think you're being a little picky here. – Pere Feb 10 '15 at 16:54
  • 2
    Kevin Ross's answer is correct. Your original answer was useless, because it referenced software that was released around 1994. Your edited answer is arguably wrong, because "Starting from Jet 3.0 there is no such limitation." implies there's no limitation on the number of concurrent connections. That's not the case. – Mike Sherrill 'Cat Recall' Feb 10 '15 at 17:42
  • The correctness of my answer, as its usefulness, is subjective. You are making the assumption that @ramu didn't need a 16-bit version of Access; Kevin Ross also things he could be interested in upgrading to SQL Server. But maybe your assumptions are wrong. And even if they were right, that would not prove who is right because you couldn't infere that from the info provided in the question. I've seen dozens of comments like "thanks but I'm not interested in nor is it possible for me to upgrade to SQL Server". Again, what's unuseful for you could be useful for others as long as it's not wrong. – Pere Feb 10 '15 at 17:57
  • @MikeSherrill'CatRecall' and no, saying that "starting from Jet 3.0 there is no 10 simultaneos client limitation" is not the same as saying that "there's no limitation on the number of concurrent connections", which I never said. Subjectiveness again. I don't know what the limitation is; what is sure is that the old one doesn't apply. – Pere Dec 15 '15 at 12:59
  • Its a fair comment because it address the origin of the 'myth' that others are trying to debunk. – greg Nov 16 '17 at 12:12
-2

In my experience 10 concurrent users starts causing problems. I'm sure there are examples w/ very small datasets working well w/ many users.

Access may be fine for some applications. There seems to be a lot of passion in this thread.

The key concept to understand here is that there is no server. EVERY QUERY will pull the ENTIRE table across the network.

If its a JOIN, EVERY QUERY will pull EVERY table involved across the network. That's because the JOIN engine is located on your desktop.

It doesn't matter where the access file is. At best, it is located on the primary user's desktop machine. Everyone else has to utilize the network to access the data.

If you have a 100k table and you want id #1042, you will pull 100k * Record length worth of data across the network and then filter out everything but #1042. It can't cache, because your coworker may have changed the next record you want to look at.

I don't think its necessarily the number of concurrent users on an Access DB. I think it's the number of people concurrently pulling sizable data chunks over the network each time they click a button.

Network load/Network latency will increase as table count grows, record counts grow,and user count grow. Possibly w/ a multiplicative effect. Compound this when you have offsite data centers (encryption), vpn users(encryption), users on different continents, etc. etc. etc.

greg
  • 1,673
  • 1
  • 17
  • 30
  • 1
    Most of this is wrong. The Access database engine uses indexes to avoid doing table scans. – Andre Jun 26 '18 at 14:14
  • I agree with @Andre. See [this answer](https://stackoverflow.com/a/31998888/2144390) for an example of a query against an ~80 MB table that only generates 110 KB of network traffic. The entire table is most definitely *not* being retrieved over the wire. – Gord Thompson Jun 27 '18 at 18:22
  • @Andre,@Gord Thompson , it may be that if the query is such that there is a covering index, then the client will only request the index. Fair distinction. But if there is no covering index, the whole table must be returned, or you have to go through all the records and get the non covered columns. There is not thread running on the file storage. You can say what you want, if I have an .mdb on a NAS device, you have to do all the work over the network. – greg Sep 16 '19 at 19:50