I have been ok with optimistic locking most of the time but I have a
situation now where there is more of a chance that users could try to
edit a record at the same time. I'd like to use pessimitic locking, but
it's not working quite as I expected. I've read all over, and by now
have posted elsewhere on this subject, and (strangely it would seem)
have made no progress. This seems to be a bit of a blank spot on the
map. (Access 2000-2003, dao)
With the form's Record Locks property set to Edited Record, if I leave
the form's record selector visible, a second concurrent user's attempt
to edit a record is immediatly flagged with the "no" (or locked) symbol.
But I don't use record selectors, and the symbol isn't very informative
for end users. I want user one to be able to start editing a record; and
if user 2 attempts to do so, be prevented from starting. How can I
detect that this record is not available for edits, and notify the user
in some manner?
The common suggestion is to turn on pessimistic locking, open a
recordset pointing to the same record, and use the .Edit method on it,
trapping the error that should arise if it's already being edited. Fine,
that works, but even with all files on one pc, it takes two seconds for
the error to be raised (why?), which is far too long.
Another supposed option that gets some mention is the dao recordset
property .EditMode but I've never been able to get it to return any
value other than 0, regardless of the acutal state of the record. So
it's not of any real use.
Finally the record selector's "locked" status flag does not seem to be
exposed as an event etc.
All of the roadblocks I've encountered and have related above have been
voiced by others in newsgroups over the years. But I've not seen a
solution. Kind of hard to believe! I'd prefer to use some native Access
mechanism for this, but if it's that busted, does anyone have a
workaround routine for this that they'd be willing to share?
