Record locking in Access 97

[Steve]

I have a shared database with 4 users. Default record
locking is set to "Edited Record", Default Open Mode is
set to "Shared", user a group permissions are all at the
default values. Updates are made via a form. Users have
just recently been having a problem where they are unable
to update records. There is no error message, only the
little "prohibited" icon appears in the left corner of the
form. Initially I assumed this was just an issue of
another user trying to edit the same record. However,
we're occassionally seeing a lot records that cannot be
updated--far more than makes sense given the limited
number of users and low volumes of updates. I'm thinking
of setting Default Record Locking on the Advanced tab to
No Locks to see if the problem goes away. Any other
suggestions would be appreciated.

Thanks

 

[Albert D. Kallal]

If the records are small, and close together, then users will find that some
records are locked and not available. Ms-access 97 does not have individual
record locking, but locks what is called a "page" of data. This means that
often sever records will be locked.

However, if those records are a sub-form, then I would not bother to lock
those records at all, since you can only get to the sub-form records by
opening the "main" record on the form.

If you can't get around this problem, then you might want to switch the
locking to optimistic. That means users will be able to edit the records,
but if two users start editing the same record, the last one out will get a
message about the record having been changed by another user.

in a2000 and later, you have true single record locking...

I did in fact roll my own locking routines in a97.

 

[Steve]

Albert, that's very useful information. I looked more
closely at the MS97 help file and it does indicate that
choosing to lock edited records may also lock other
records "that are stored nearby on your disk." Anyway, in
referring to "optimistic" locking, do you mean choosing
the "no locks" option?

Thanks again

 

[Albert D. Kallal]

Yes....no locks is what I did mean.

I always disliked some "jargon"..and now it is scary...as I am starting to
use jargon!