Multi User Acess 97 Freezing Problem

[lizI3]

Hi,
We are running a multi user access 97 db from a server. I have set up the workgroups etc. Our problem is that if more than one person is entering into the DB at one time after a few minutes it Freezes. Has anyone else experienced this problem and if so how did you correct it.

Iam not sure if it is a Database problem or a server problem. The server that it is on is quite full

Thanks

Liz

for reference I have also posted this on Ask Mr. Excel - Access Questions

 

[RJFost]

Could it be a locking issue? If your users are updating records from the same table at the same time, you might want to change your locking options for that database. By default, Access uses "pessimistic" locking; when a user starts to edit/add/update a record, Access locks the page containing that record. If you switch to "optimistic" locking, Access will only lock the record being changed & that only at commit time.

 

[lizI3]

Thanks RJFost
I when to tools options Advance and changed the locking option from "no locks" to "edited record" I also make sure that the open database using record level locking option was tick for yes. Is this what you were refering to as "optimistic" locking or is there another area I should be looking at. Should all my forms and queries have the record locking option set to "edited record" or is setting it a the DB level good enough?

Thank you very much for your help
Liz

 

[lizI3]

The database is being used as a interface to another system. Data is keyed into the DB using dropdown boxes and then exported to a text file and imported into another program. Therefore the data is not really being edited just keyed in to the table. The only time it may be edited if the user made an error and corrected it, but the would be to their own entries. But all user (4) are keying into the same table. Only one does the export. This said what should the locks be set at?

Iam getting a bit confused with the locks.

Thanks
liz

 

[psemianonymous]

There are many links on the chain, so to speak. A lot of different things can go wrong, some of them unrelated to Access itself.

I see a few (potential) problems:
1. File locking settings. Access databases work better (or, should I say, cease to error) sometimes when you disable optimistic file locking. This is set in the registry of the file server on the network. I recommend you read a *lot* about this before deciding to make the change; Microsoft has a bunch of MSKB articles about "optimistic file locking".

The type of behavior you described is something like I've seen. Did you see any specific error messages? I get the 3043 - Disk Or Network Error messages.


2. Using an AutoNumber Primary Key for your main table. If multiple people are entering data into the table, they can simultaneously grab the same primary key value. This is bad. It leads to database corruption, which is NOT the behavior you've described...at least, not exactly.


3. Probably several things I've forgotten about.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm