
Record Level Locking Possible with Access?
Quote:
>Does anyone know if it's possible to do RECORD level rather than
>Page level locking using VB and Access formatted databases?
>If so, how, or where can I look to find more info?
I do it all the time, as I write multi-user applications in which page locking
is totally unacceptable. The basic idea is to:
1. Add a boolean field to each table that requires row level locking.
2. When a user extracts a record from the database, check the above-
mentioned boolean field. If its set, give the user a message that
the record is being edited by another user. If its not set, set it and
let the user edit the record.
3. When the user has finished his editing, clear the above-mentioned
boolean field.
4. When your application is started, attempt to open the database first
in exclusive mode. If this succeeds, it means noone else is using the
database, so then clear any of the locking flags that may have been
left set due to an instance of the application terminating abnormally.
5. An finally, do not use bound controls, and perform the edit method to save
the updated data just before moving the updated data into the edit buffer
and performing the update method. In other words, keep the real data-
base (page) locks open for the shortest time possible. You won't
have to worry about record changed or deleted errors (assuming you
perform operations on tables, as opposed to dynasets), as the above
record locking scheme makes these errors moot.
You can set up the record locking operations in subroutines which makes the
in-form code very clean. If you are interested I'd be happy to e-mail you
an example or two.
Richard Schaefer