Sidestep Access File Sharing Lock Count Limitations

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.

Advertisements

3 responses to “Sidestep Access File Sharing Lock Count Limitations

  1. Reminds me of single user mode in Progress.

    Like

    • My thought too, but perhaps allowing for data corruption by allowing other processes to update without locks. Might dig a bit deeper on what behaviour we can expect from that flag.

      [edit]: Seems it’s a “coarse-grained locking” flag. I’ve updated the blog entry to match.

      Like

  2. I came across this problem when the database client was running on a file share. The only resolution was to run the Access client database on the local hard drive.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s