Maximum concurrent connections in ms access

Asked 13/4, 2011 at 9:4 Answered 16/11, 2017 at 12:50

What are the maximum concurrent connections ms-access supports?

Capping answered 13/4, 2011 at 9:4 Comment(5)

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. – Osteoclasis 13/4, 2011 at 9:8

@Steven: s/correct/corrupt/ ? – Lasser 13/4, 2011 at 9:30

@Mat: You are right about the typo. I mean "corrupt" not "correct". – Osteoclasis 13/4, 2011 at 9:39

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. – Freeswimming 14/4, 2011 at 3:32

@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. – Brazen 22/8, 2013 at 20:41

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.

Katabatic answered 13/4, 2011 at 11:3 Comment(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

Paulenepauletta answered 25/6, 2014 at 15:49 Comment(8)

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). – Manure 10/2, 2015 at 2: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. – Paulenepauletta 10/2, 2015 at 8:57

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. – Manure 10/2, 2015 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 @Lasser 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. – Paulenepauletta 10/2, 2015 at 16:54

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. – Manure 10/2, 2015 at 17:42

The correctness of my answer, as its usefulness, is subjective. You are making the assumption that @Capping 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. – Paulenepauletta 10/2, 2015 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. – Paulenepauletta 15/12, 2015 at 12:59

Its a fair comment because it address the origin of the 'myth' that others are trying to debunk. – Isidraisidro 16/11, 2017 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.

Isidraisidro answered 16/11, 2017 at 12:50 Comment(3)

Most of this is wrong. The Access database engine uses indexes to avoid doing table scans. – Idalla 26/6, 2018 at 14:14

I agree with @Andre. See this answer 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. – Sleuthhound 27/6, 2018 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. – Isidraisidro 16/9, 2019 at 19:50

Recommended topics

Hot tags