concurrence process

how many users can use the Access at the same time and not affect the
spped
LVL 1
tanc02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianWrenCommented:
That's a question that can't really be answered.  It of course depends on the network that's involved, (and the other traffic on it), what operations are being carried out, etc.

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
0
tanc02Author Commented:
can you tell me more about locking the database
0
wesleystewartCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tanc02Author Commented:
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 ?
0
wesleystewartCommented:
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
0
tanc02Author Commented:
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
0
wesleystewartCommented:
No problem.

3 good urls for optimization:

http://www.fmsinc.com/tpapers/faster/index.html
http://www.fmsinc.com/tpapers/vbacode/index.html
http://www.microsoft.com/officedev/articles/movs101.htm
http://www.microsoft.com/accessdev/articles/v35perf.htm

Concurrency?  

You are sort of on your own there.  If you bump into any issues, your friends at EE will always be happy to help.

Wes
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanc02Author Commented:
what do you mean by optimization
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.