In my current role, I have co-workers who are required to make extensive use of Microsoft Access databases. These fellow co-workers are at a bit of a disadvantage though, in that they don’t know what it means to have a “relational” database. “Normalisation” is just a meaningless word to them. For the most part they get by, regardless, and only call me in when they hit oddities. Such as yesterday’s issue.
A co-worker was recently given an updated set of river flow data spanning decades. In their eventual rightful home, we’re talking several tens of thousands of records that needed updating. We needed to find a relatively error-free, manual way of doing it where she didn’t need to revert to a lengthy SQL statement to do it. We found a way, but Access choked on the number of records we were wanting to update. Specifically, we hit a warning box in Access that said “File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry.”
Playing with Windows registry entries is a last-resort option for me, so we went looking for alternatives. Official Microsoft support on the issue lists two ways. One via the registry, another via Access as a temporary override. We tried this “override” option with a succession of larger and larger increases to this “maximum locks per file” limit, but we kept hitting the same error.
Eventually, whilst trawling though application options for Access, I found a “Open databases by using record-level locking” check-box. I unchecked the box, and we found ourselves successfully copying the tens of thousands of rows of data from one table to the other. The checkbox itself switches between coarse-grained “memory page” locks and fine-grained “record-level” locks, so what I’ve really done by tweaking this flag is to decrease the number of locks needed for an update, with the tradeoff that if some other process wants to update the data, it may be held up waiting for me to release a lock on a record that I’d finished (or never) updated.
At the time that I was doing this, I wasn’t aware that Access allowed “Exclusive” mode for opening a database. Theoretically, in exclusive mode, locks aren’t an issue so I’d probably have had success taking this approach as well.