Jet 3.51 with VB5: Large SQL update query misses some records
===================================================
A colleague is working with a large Access database (100,000+ records)
and is using an update query across joined tables (e.g. Update
ThatField to  X where ThisField = Y).

First problem: It's taking Access/Jet 30 minutes to process the query.
And second, at the end, Access/Jet reports that approx 900 records
couldn't be updated due to record locks. There are no record locks.
The database is opened for exclusive use!

Any ideas why this is happening? I searched the KB but found nothing
much, apart from a mention in one Q article that a MaxRecordLocks
value in the registry could be increased, although that was referreing
to a different, though vaguely similar, problem.

Cheers!

Jake

Here's a wild guess for question 2: Access locks things in pages of 2K (or
maybe 4K), not by row. So, if you are updating row 100 in the table, it
could be that dozens of rows on either side of it in the same page are
locked until the update completes. Then, it could be (again, just a guess),
that Access partitions giant updates into smaller subtasks and that some of
these smaller chunks are colliding with other (already locked) pages as they
try to execute.

For problem one: are you using a stored query in Access to do the update?
Using a stored query instead of dynamic SQL may improve the time to process.