Optimistic Locking

[Davie]

I have created an Access 2000 database with a user form on
the front end. The user form has 10 text boxes, 4 combos,
all linked to a table. The combo's can be changed and the
record saved. I also have an embedded form in DataList
view to show whats in the table.

When a user opens this form it shows that the file has
been locked with 10 - 20 write locks on the network admins
little diagnostic utility.

There is nothing in the code to open writes to the table
so it seems to be the form itself generating them.

Does any one have an explanation of this or know if this
is a problem or not as my network admin seems to think it
is and the database frequently becomes corrupt with an
error saying its not a valid database file. A repair and
compact works but we have to log all 20 users out of the
system to do it.

Thanks

 

[Microsoft]

To much of an optimist I think, I meant Opportunistic...oops

 

[TC]

I have created an Access 2000 database with a user form on
the front end. The user form has 10 text boxes, 4 combos,
all linked to a table. The combo's can be changed and the
record saved. I also have an embedded form in DataList
view to show whats in the table.

When a user opens this form it shows that the file has
been locked with 10 - 20 write locks on the network admins
little diagnostic utility.

There is nothing in the code to open writes to the table
so it seems to be the form itself generating them.

Does any one have an explanation of this or know if this
is a problem or not as my network admin seems to think it
is...

Access (really, Jet) has optimistic locking, & pessimistic locking. This
determines whether an edited record is locked just before it is written to
disk, or as soon as the user has typed the first change, respectively. Also,
it can use record-level locking (where only the single edited record is
locked), or page-level locking (where a whole 2kb page of data containing
the edited record, is locked). But AFAIK, none of this should come into play
until the user (or VBA code) >does try< to edit a record. So if nothing is
trying to edit a record, none of those locks should take place, IMO.

Extra to the above, if the db is opened in read/write mode, Jet will place
at least one lock on the so-called workgroup information file (which
typically has an MDW extension). Also, I'm guessing that Jet might well
place other locks on other parts of the MDB file, for its own (internal)
purposes.

So I'm not sure you could really conclude very much, from those write locks.
What is your network administrator suggesting? That Access or Jet has "gone
wrong", & is unncessarily placing write locks on files that it shouldn't? A
doubtful proposition, IMO.

... and the database frequently becomes corrupt with an
error saying its not a valid database file. A repair and
compact works but we have to log all 20 users out of the
system to do it.

This is almost certianly due to other problems such as, dropped network
connections, the db not being split into a fron end/back end structure,
and/or each user not having their own copy of the front end.

HTH,
TC