Hi,

I am having multiple users logging into an Access 97 database. They need to be able to each grab a single record, edit, update and grab another new record. The record grabbed by an individual user needs to be unique.

I have tried a lot of different ways to do this bu to no avail as the problem will definately happen when 2 users try to grab a record within a fraction of a second.

I am currently using a soft locking method and Accesses optimistic locking. As in :
1) seek a new record with no soft lock present
2) attempt to edit record to insert soft lock and update
3) use On Error Goto statement to detect if step 2 encounters any optimistic locking issue. If it does, it'll jump to step 1. Else it will insert the softlock and update the record so that other users cannot lock this record.

THIS DOES NOT WORK.

Sigh..... I've been stuck with this problem for about 9 months and have been reading a lot of books and trying different methods but have been unsuccessful.

In other words..... HELP!!

Any help will be much appreciated.

In the sub or function where you execute your sql statement, add some additional error handling. In your error routine, check for the err.number. If it is a locking error, do an err.clear and use a goto (did I just say that) to a label just above your sql execute statement and let it try again.

To avoid a possible endless loop, put a counter in there and if it get to 10 or whatever you think is acceptable, allow the error to raise.



Brian T. Wiehoff