tanc02
asked on
concurrence process
how many users can use the Access at the same time and not affect the
spped
spped
ASKER
can you tell me more about locking the database
tanc02:
Like BrainWren said, the maximum number of users depends on what the users are doing with the database. The fact that there is no set answer aggravates many developers who are used to more "sophisticated" applications. The absolute maximum number of users is 255, limited by the size limit of the file Access uses to keep track of locks. You'll find, however, that Access performance will start to deteriorate past a certain number of users, depending on how the application has been optimized. I have Access apps running well with over 20 concurrent users who are adding, deleting, and maintaining records constantly. Occasionally this app runs into concurrency issues, but I'm sure with the right optimization I could get 35 or 40 users into the app without any problem.
A poorly designed Access app will croak with 2 or 3 users in it.
Of course, if your users are only viewing the data and aren't adding or deleting records, you could get much closer to that 255-user limit.
Wes
Like BrainWren said, the maximum number of users depends on what the users are doing with the database. The fact that there is no set answer aggravates many developers who are used to more "sophisticated" applications. The absolute maximum number of users is 255, limited by the size limit of the file Access uses to keep track of locks. You'll find, however, that Access performance will start to deteriorate past a certain number of users, depending on how the application has been optimized. I have Access apps running well with over 20 concurrent users who are adding, deleting, and maintaining records constantly. Occasionally this app runs into concurrency issues, but I'm sure with the right optimization I could get 35 or 40 users into the app without any problem.
A poorly designed Access app will croak with 2 or 3 users in it.
Of course, if your users are only viewing the data and aren't adding or deleting records, you could get much closer to that 255-user limit.
Wes
ASKER
For wesleystewart :
You write :
The absolute maximum number of users is 255, limited by the size limit of the file Access uses to
keep track of locks.
What do you mean by 255 ? Like 255 employees
access the database ?
What do you mean by size limit ?
You write :
The absolute maximum number of users is 255, limited by the size limit of the file Access uses to
keep track of locks.
What do you mean by 255 ? Like 255 employees
access the database ?
What do you mean by size limit ?
Access creates a file in the same directory as the .mdb to keep track of users in the database. This file will have the same name as the database file but with the suffix .ldb rather than .mdb.
The maximum size of this file, by design, is 16K. Each user's info in the file takes up 64 bytes (the first 32 bytes are the computer name of the user, the second 32 the security name). The maximum number of concurrent users, therefore, is 255 (255 users * 64 bytes = 16320 bytes, or 16K).
This means that there is no possible way to ever have more than 255 users in the database at the same time. In practice, however, a shared Access database probabaly won't support 255 users. It will be so busy keeping them out of each other's records that performance will suffer horribly or it will crash. As I noted before, a well-designed database built by a good Access developer can handle a LOT of users. A poorly-designed database will fall apart with 2 or 3 users.
The maximum size for an Access97 database file is 1gig. That's just how it is. The maximum size for an Access2000 database file is 2 gigs. You can develop applications larger than the size limit by putting data tables into different database files and then linking to the data.
In short, the maximum number of users depends on what they are doing and how well the database is designed.
The max file size rarely becomes a problem (1 gig is a LOT of data) but if it is, you can easily get around it by moving data into different database files.
Wes
The maximum size of this file, by design, is 16K. Each user's info in the file takes up 64 bytes (the first 32 bytes are the computer name of the user, the second 32 the security name). The maximum number of concurrent users, therefore, is 255 (255 users * 64 bytes = 16320 bytes, or 16K).
This means that there is no possible way to ever have more than 255 users in the database at the same time. In practice, however, a shared Access database probabaly won't support 255 users. It will be so busy keeping them out of each other's records that performance will suffer horribly or it will crash. As I noted before, a well-designed database built by a good Access developer can handle a LOT of users. A poorly-designed database will fall apart with 2 or 3 users.
The maximum size for an Access97 database file is 1gig. That's just how it is. The maximum size for an Access2000 database file is 2 gigs. You can develop applications larger than the size limit by putting data tables into different database files and then linking to the data.
In short, the maximum number of users depends on what they are doing and how well the database is designed.
The max file size rarely becomes a problem (1 gig is a LOT of data) but if it is, you can easily get around it by moving data into different database files.
Wes
ASKER
For wesleystewart :
what URL will teach me to optmize the database ?
what URL iwll teach me more about concurrence ?
Please post your comment as asnwer
what URL will teach me to optmize the database ?
what URL iwll teach me more about concurrence ?
Please post your comment as asnwer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what do you mean by optimization
There are things that you can do to speed things up, (transactions, optimistic locking, good database design, restricting how many records get processed at a time, etc., etc., etc.).
Brian