How many (max) users can Access support?

Before I start in, let me clarify one thing: we are talking about the multi-user capabilities of the JET database engine. Known to everyone as "MDB files". If you are storing your database data (called the database backend) on something other than MDB files, this does not apply to you. Stop reading this, and go look up that DBMS's documentation on concurrent users.

----------------------------------------------------------

Code:

INTRODUCTION
____________

Access can theoretically support 255 concurrent users, but in practice falls short of this. The more precise answer is "It depends."

First, know that there is no scientific basis for computing the maximum concurrent users an Access database will support. No one, to my knowledge, has ever run endurance tests or burn-in tests for Access database applications. So the short answer to your question is "I don't necessarily know." Maybe your database will work with no more than twelve (12) users. Maybe your database can support several hundred concurrent users successfully.

Code:

A BRIEF ESTIMATE
________________

And now, for a brief estimate. Access can support at least TEN CONCURRENT USERS. I have no hard evidence to back this up, but neither does anyone else! I base this on purely anecdotal evidence--I have seen lots of evidence supporting this, and a complete lack of stories stating that Access cannot support ten users. Let me restate: You should have ABSOLUTELY NO PROBLEMS WITH TEN OR LESS CONCURRENT USERS.

Code:

MULTI-USER PERFORMANCE FACTORS
______________________________

Before I move on to a more anecdotal evidence, let's go down to the low-level and consider what happens when users access the database. They open the Access session on their computer to a locally-stored frontend Access database file. The frontend MDB (or MDE) file stores the queries, forms, reports, code, and links to the backend. The backend MDB (data) is stored on a file server on your network. No, let me split that up: the data is stored on a file server, and is transmitted over the network. Now you see that there are three possible bottlenecks:

1. The user's computer
2. The network
3. The file server

When you add more users, you are taxing #2 and #3. If you have a 10Mbps network, which many of us do, your bottleneck can be the network itself. If you have one file server that is already overtaxed, you may notice slow performance with even one user. If your users are running a Pentium-133 running NT4 on 32MB of RAM on a network, it will probably take a lot longer for them to get anything done then your overclocked quad-P4 3GHz users.

(CHANGED 2003-12-22) For an excellent discussion of both JET in a multiuser environment as well as a comparison to MSDE as a backend in a multiuser environment, check the following (recent as of today) newsgroup thread: http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&rnum=1&thl=0,995547155,995541268,995524195,995503509,995403053,995234076,995003755,994830754,994755882,994511090,993371942&seekm=5cbf60ef.0312101918.4a62e17a%40posting.google.com
(I could probably write the FAQ based on the information given in this thread alone. For further reading, definitely check this out)

Code:

DATABASE SIZE AND MULTI-USER PERFORMANCE
________________________________________

When you have a small database, the data transmitted is a relatively small amount. Larger databases will perform slower than tiny databases--it is the simple truth.

This is not to say that large databases are unusable--I have personally seen (here we go again, more anecdotal evidence!) an unsplit 350MB database doing fine with multiple concurrent users, and have read about many more databases of that size or greater. Just be sure you do not approach the JET database size limits:

Code:

IMPROVE YOUR PERFORMANCE
________________________

The golden rule of multi-user performance is to minimize data transferred over the network.

Avoid bound forms on large tables (thousands of records or more).

If you really want to improve performance, use all unbound forms and disconnected recordsets, to absolutely minimize data transmission--by doing so, you will rip out many of Access' built-in features (such as searching, sorting, filtering, and the default behavior many users are accustomed to).

Code:

SIGNS THAT YOUR DATABASE HAS REACHED ITS LIMIT
______________________________________________

This is quick and easy. You will see either one or both of the following:

--Dramatic drop of performance/speed

--Recurring database corruption

NOTE: There may be other factors contributing to slow speeds, and especially database corruption. Databases can be (repeatedly) corrupted by a single faulty network device, so if you see lots of database corruption after adding one user, consider the possibility the user's NIC is damaged. It's at the least a good possibility.

Code:

THINGS YOU NEED TO KNOW
_______________________

1. If you have users connecting over the WAN, meaning Wide-Area, then you WILL HAVE PROBLEMS. The standard solution for WAN users is to have them connect to a Windows Terminal Server (and/or Citrix MetaFrame)--connect to a terminal server on your LAN, which then accesses your database. There can be problems with the terminal server solution as well--this from personal experience.

2. ADP's are a nebulous and tricksy subject for me. I am an Access 97 dinosaur, so I am completely blind to ADP's and their joys. From what I have read, ADP's allow for better multi-user performance (allow more concurrent users) than a straight MDB frontend. So go seek the knowledge (by yourself: forum958 ).

3. Database security and database corruption are both issues you will have to deal with in a multiuser environment. For the long version, check this FAQ: faq181-3893 . The short version: a) JET is a bad way to store mission critical data, as it can corrupt your data leaving you with only last night's backup; b) JET is a fundamentally insecure way to store your data, but you can prevent casual intrusions. Depending on where you look, you can crack access security for $free-$150. Without fail.

4. For general performance tips, check out Tony Toews, a Microsoft Access MVP, and his excellent website on database performance tips. Many apply to multi-user situations, and many performance issues you didn't even know about from running the database locally will crop up when you put the database on a network share. You might be surprised. See: http://www.granite.ab.ca/access/performancefaq.htm