record locking

[LauraK]

Acess 2000. I have a multi-user database and I want to institute record
locking. I do not want two users to be able to edit the same record at the
same time

I've read all the stuff in the help menu and think that I have to use
pessimistic locking. I followed the instructions and set the options to
edited record level locking and open databases using record-level locking.
However, in testing this, two users can make changes to the same record at
the same time. If the users are making changes to different fields (in form
view) then the changes pop up on the other users screen. If both users are
making changes to the same field and one user tries to save after the other
user has already saved we get a Write Conflict message that says:

The record has been changed by another user since you started editing.
If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the
other user enter ,and then paste your changes back in if you decide to make
changes.

We have 3 options:
1. Save Record - Ignores/deletes any changes the other user made.
2. Copy to Clipboard
3. Drop Changes - Ignores/Deletes changes you made.

That's cool and all, but we can't get the copy to clipboard feature to work
right...

The bottom line is that I want only one user to be able to edit a particular
record.

 

[TC]

Optimistic & pessimistic locking are /both/ techniques for stopping two
users editing the same record at the same time.

Say user 'A' starts to edit a record but then goes to lunch before
saving the edit. User 'B' then queries the same record & tries to edit
it.

With pessimistic locking, user 'B' is given the bird - immediately. So
at least he does not waste his time by typing changes that can't be
saved. The record was locked when 'A' started typing his changes. It
stays locked until 'A' comes back & save the change, or discards it.

With optimistic locking, I believe that user B will be able to make his
change. Then when user 'A' returns, & tries to save his changes, /he/
will get the bird, so he has wasted his time by typing those chnges -
he can not save them now.

If clashes are /likely/, go with pessimistic locking. If unlikely, go
with optimistic. But either one will stop two users from editing the
same record at the same time, in such a way that one user's change
unexpectely overwrites the other user's.

In adition, Access 2003(?) introuced tru, record level locking. Pior to
that, you are actually locking a whole 2kb or 4kb /page/ of records at
a time. That might be 1 record, or 2, or lots, depending on the record
sizes.

HTH,
TC (MVP Access)
http://tc2.atspace.com