Muilty-user locks Records

[Silvio]

Hello, my Access database is spitted in back-end and Front-end, each user has
the front-end installed in their own computer. The Database property on the
Back-end and Front-end are as follow:


1. "Open Mode" to "Shared",
2. "Default Record Locking" to "Edited Record".
3. "Open Databases Using Record-Level Locking" is also checked.


The Front-end users use a form to view a list of records assigned to them to
work on. Each record have a Combo Box linked to a table (tblDisposition)
located the back-end so users can make selections (Approved, Disapproved or
Cancel) and store the selection in the data table (tblData) in order to
fill-in data in records assigned to them.

The problem is that occasionally several users complain that they can open
the combo box and they can see the 3 options listed, however they are unable
pick a selection in order to update the record. It appears like “all†the
records in their list are locked in a read-only monde. It is acting like if
the "default record locking" was set to "All Records". However, this is not
the case I only have "Edited Records" and the problem appears to be
inconsistent. If I ask all the user to close their database, then the record
can be updated by the user having the problem. Does any one have any idea to
what can cause this problem and how to solve it?

 

[Allen Browne]

Try changing Default Record Locking to None, and give your users
instructions on how to handle the conflict dialog.

It sounds like the db may be using page-level locking anyway. My
understanding is that the locking strategy is
If the FIRST user into the db, so if that user is page-level, that's what
you will get regardless of what setting you have on your machine.

IME, there are some cases where record-level locking fails (update queries
in a transaction that cannot run to completion until you turn it off), and
it also has some performance penalty. My suggestion would be to try without
it, and use record-level locking only if needed.

But Optimistic locking (labelled "None") may be what you need.

 

[Silvio]

Thank you Allen, all my users are using Access 2003 Page-locking appears that
it was a problem with Access 2000.

 

[Silvio]

Allen, I have one more question for you if you don't mind. My database has a
back and front end, where the Record Locking should be selected in the front
end or back end? What happen if the back and front end have conflicting
Record Locking selections?

 

[Allen Browne]

My expectation would be that Access would use the locking stategy of the
front end (that is the first front-end that attempts to open the back end in
any session.)

 

[David W. Fenton]

What happen if the back and front end have conflicting
Record Locking selections?

Where, exactly, does one set the record locking in a back end?

Is it not the case that the setting is an Access setting (that
determines the default locking of any MDB that is opened in that
instance of Access) or a setting on a particular form. In the former
instance, it's the Access application that's doing it (not your
front end or back end) and in the latter case, it's your form.