Maximum users in Access97

[Barrow66]

I am using Access in a multiuser environment. The database is used for users accessing information by calling into the system.Users at the same time run reports and do package ordering.
The database get corrupted many times in a day. How can this be averted.
How much users can access accomodate in a multiuser environment when SELECT, UPDATE and INSERT are done simultaneously?
Does anyone have a better solution to this problem.

Thanks in advance

 

[KenReay]

Hi

The theoretical maximum is 255, but the practical limit is much lower.

It is difficult to be precise, since so much depends on the quality of the hardware/network infrastructure, how well the application has been written and what the users are actually doing (ie updating or viewing).

If you search past posts on this topic you will get answers ranging from 4 users to 100+ users.

I can only give you my own experience, which is that 40 plus users is quite acceptable, but the if you have more that 8-10 uses doing simultaneous updates you are like to encounter problems of database corruption. The frequency depends on how far over the threshold (8-10) you are.

As I say, this is not 'provable' advise, just based on my own experience.

If you are over this sort of number, you need to be thinking of moving to a client server model for the backend (eg SQL Server, MYSQL whatever).

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GDGarth]

I agree with Ken. There are, however, a number of things you can do to greatly reduce the number of crashes:

1. Split your database into a front end / back end, if you haven't already done so. This is the single most important thing to prevent corruption, IMHO. It works best if the front end is on the local computer, although I've got several where the front end is shared on the network drive.

2. Set up your database so a user is bumped off automatically after some period of idle time - or at lease so data entry or edit forms are closed after being idle. Some installations of Access seem to have problems if left open and unattended for long periods of time. If the computer locks up and the operator needs to kill it, your database may be corrupted.

3. If you have a number of users updating or adding data simultaneously, consider having the changes or updates saved to the local hard drive, and sending them to the network drive periodically. This will reduce the number of conflicts, and, if structured correctly, will make your data more secure.

4. Look at your record locking strategy. Make sure a record is locked while being edited. Two users modifying the same record is a sure recipe for disaster.



A properly constructed database is way more stable than a poorly constructed one. You don't have to be an expert in VBA to set up a stable, reliable database.