"Lucy" wrote
Is there a limit as to the number of
users that can access one database?
I have a database (access 2000) that I
developed with around 10 users in mind.
Now, they would like to expand access
to around 50-60 users.
Is there a limit? Will this cause sharing
violations?, etc.
As others have said, the physical limit is 255.
There are many factors in performance and stability. If all of them are
near-perfect, we have reliable reports of a split Access - Jet database
supporting over 100 concurrent users. Even if not all are near-perfect, but
it's a well-done database, reports of 30 - 70 users are not uncommon. On the
other hand, we've had reports of "Access falling over with 4 users" -- my
personal opinion is, that if all the factors are as far from perfect as
imaginable, and someone puts ingenious, inventive effort into the poorest
design and implementation possible, that you can create an Access database
that will not support even ONE user.
But, I caution (as you have noted) that initial estimates of users are
almost always low, often a great deal low. I worked on one project which,
when I came on board, had 35 users and the client said, "Never be more than
50." Five years later, there were between 175 - 200 users.
If I had a client who had, so soon, expanded from 10 to 60 users, I'd
probably advise that the initial implementation be an Access client (not
multiuser) application using the (free) desktop version of Microsoft SQL
Server, aka MSDE. If the concurrent user load then overwhelemed MSDE, it
would be a trivial change to move to one of the (not-free) editions of full
Microsoft SQL Server.
On the other hand, you or the database owner could opt to try a multiuser
implementation. Many of the techniques for optimizing multiuser are similar
to those used in creating a client. Thus, converting an operating multiuser
DB to an Access client with SQL Server is often a lesser task than
converting a single-user database. If you decide to take that approach, the
following may be useful.
There's an introductory presentation on Access in a Multiuser Environment
that I did for my user group that you can download from
http://appdevissues.tripod.com. It will identify topics that I thought
worthwhile to discuss, and a bit more. The best collection of detailed
information and links on the subject of Access in the multiuser environment
is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.
If you decide to start with an Access client application, I have a
presentation on that subject, too, at
http://appdevissues.tripod.com. And,
if you use the Access MDB with Jet and ODBC connectivity, you are not
limited to Microsoft servers, but can use any that is/are ODBC-compliant. On
the other hand, as far as I know, the Access "project", or ADP, is strictly
limited to Microsoft SQL Server. Oracle has an "upsizing wizard", but I
don't know if it creates an ADP.
Larry Linson
Microsoft Access MVP
