Best way to deal with record-locking conflicts

[Brian]

Is there a best practice or are there design elements to use or avoid
regarding the interception and & processing of record-locking conflicts
during mass update processes?

I already trap error 7878 on forms where users may attempt to edit the same
record simultaneously, with two different users opening the same record
simultaneously. This is not a big problem because I can intercept the update
and notify the user of the conflict as the user is interacting with that
single record. However, when one user runs a query or SQL statement or opens
a recordset that will update many records, it is always possible that some
other user may have one of those records locked on a single-view form.

I can explain best by example: In my current project, I have (among many
others) a customer table and a route table. RouteID is the PK in the Route
table and an FK in the Customer table.

If one user has a particular customer record open via a bound (single) form
and another user attempts to do a mass move of customers, including that
customer, to a different route, it is fairly easy to deal with the user
having the single customer form open, but if the mass-update user experiences
the record-locking error, it will most likely occur in the middle of a query
affecting 150 records or, worse yet, after three or four steps of a process
involving dependent steps are completed, leaving some process half-done with
no way to determine the prior state of the individual records.

Is there, for example, a way to prequery and place a lock on all the records
to be included in a recordset or query?

 

[Clifford Bass via AccessMonster.com]

Hi Brian,

I do not know about pre-determining if any records are locked. But for
a multi-step process that needs either to succeed as a whole or fail as a
whole, you will need to use transaction processing. Something like:

Public Sub DAOTransProc()

Dim db As DAO.Database
Dim ws As DAO.Workspace

On Error GoTo Handle_Error

Set db = CurrentDb
Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
db.Execute "sql statement 1"
db.Execute "sql statement 2"
db.Execute "sql statement 3"
ws.CommitTrans

Exit_Sub:
Set ws = Nothing
Set db = Nothing
Exit Sub

Handle_Error:
MsgBox "Error #" & err.Number & vbCrLf & vbCrLf & err.Description
If Not ws Is Nothing Then
ws.Rollback
End If
Resume Exit_Sub

End Sub

In this example, only when there are no errors at all do the changes get
committed. And then they get committed as a whole. So if someone accesses
one of the tables after you have started making changes, but before you have
completed, that person will not see those changes. Or may even be blocked
from accessing those records that have been changed. If any error happens
during the process, all changes are rolled back so that the database is in
the state it was before the process started. Transaction processing is also
available in ADO.

Hope this helps,

Clifford Bass

 

[Tom van Stiphout]

Clifford already gave you the solution. And no, there definitely is no
way to pre-query for locks. Think about it: if that were possible, it
is also possible that the situation would have changed the millisecond
after you perform such query, so that information is not helpful.

As Clifford says: you process action queries (plural) in a
Transaction, and you handle whatever errors may be thrown.

-Tom.
Microsoft Access MVP

 

[Dorian]

Mass updates must be in a transaction.
You can Rollback if an error is encountered.
Read up on this in Access HELP.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".