How to prevent locks?

[hanski]

Hi.

User1 opens a record by a form.

User2 opens exactly the same record by a form.

User2 changes information in that record and when he wants to close
the form, Access says that someone already has...and so on... and
change does not succeed and everything he wrote disappear.

Is it possibe to tell User2 ,already when he opens the form, that
someone has already locked the record and you can't change anything?

hanski

 

[John W. Vinson]

Hi.

User1 opens a record by a form.

User2 opens exactly the same record by a form.

User2 changes information in that record and when he wants to close
the form, Access says that someone already has...and so on... and
change does not succeed and everything he wrote disappear.

Is it possibe to tell User2 ,already when he opens the form, that
someone has already locked the record and you can't change anything?

hanski

Yes. Set the "Locks" property of the Form to "Edited Record" to invoke
pessimistic locking. The second user won't be able to edit anything.

 

[hanski]

Yes. Set the "Locks" property of the Form to "Edited Record" to invoke
pessimistic locking. The second user won't be able to edit anything.

Thanks, but it don't work at me because I have MSDE database via ODBC
links and not Access MDB-database. Maybe I have to do something in
MSDE...

hanski

 

[Tom van Stiphout]

On Wed, 1 Oct 2008 05:22:45 -0700 (PDT), hanski

Then you're probably best of with a "logical lock". This is where you
do all the work yourself. When User1 accesses a record, you write a
record to a "tblLocks" table (probably PK and UserID). Then when
another user accesses a record, you check with this table and if a
record there, you set the form to readonly (Form.AllowEdits etc).
Then when User1 leaves the record, you delete the tblLocks record.

One problem with this approach is when User1 crashes and never gets a
chance to delete the lock record. That's why you can also add a
datetime to tblLocks and automatically clear all locks for the user
when she starts the application again, and also when the lock record
is more than N minutes old.

The Lock fields can also be maintained in the primary table.

Note that I do not advocate this design; I am just answering your
question. 99.9% of multiuser applications take a different approach
which is optimistic locking and the last save wins.

-Tom.
Microsoft Access MVP

 

[hanski]

On Wed, 1 Oct 2008 05:22:45 -0700 (PDT), hanski


Then you're probably best of with a "logical lock". This is where you
do all the work yourself. When User1 accesses a record, you write a
record to a "tblLocks" table (probably PK and UserID). Then when
another user accesses a record, you check with this table and if a
record there, you set the form to readonly (Form.AllowEdits etc).
Then when User1 leaves the record, you delete the tblLocks record.

One problem with this approach is when User1 crashes and never gets a
chance to delete the lock record. That's why you can also add a
datetime to tblLocks and automatically clear all locks for the user
when she starts the application again, and also when the lock record
is more than N minutes old.

The Lock fields can also be maintained in the primary table.

Note that I do not advocate this design; I am just answering your
question. 99.9% of multiuser applications take a different approach
which is optimistic locking and the last save wins.

-Tom.
Microsoft Access MVP






- Näytä siteerattu teksti -

Good idea Tom! Thanks!

hanski