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?

 

[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".