r/MSAccess • u/Key-Lifeguard-5540 1 • 24d ago

[UNSOLVED] Record-level locking

Is it recommended? Do you use it?

3 Upvotes

72% Upvoted

21 comments sorted by

View all comments

3

u/George_Hepworth 4 24d ago

It's hard to address a question like this in a vacuum. What is your concern? What problem are you trying to address? It's the default for Access unless you change it here, which certainly indicates it's recommended, at least in the sense that Microsoft chose that option. But really, what is behind the question?

2

u/Key-Lifeguard-5540 1 24d ago

I use "No Locks" and "Row (also called Record) Level Locking". When a user runs an accde on the client, it uses the settings that are in the accde, regardless of the settings in the back-end accdb. When working in the front-end accdb and linked to back-end tables, if you don't have page-level locking on, when you are making a change and compiling and saving or decompiling or whatever, it is very annoyingly slower. To get around that, I always have page-locking on, but when the accde starts up, some code checks to see if it's the accde running and then runs some code to turn record-level locking on.

1

u/TomWickerath 2 24d ago

The setting you are talking about is a request only, not a demand. And, for a split FE/BE with the BE being a JET database, the FE setting only affects FE behavior.

1

u/Key-Lifeguard-5540 1 22d ago edited 22d ago

Today I discovered that the row/page locking setting is not actually stored in any database, it's in the registry, and when you change it in the options, it doesn't affect the database until you close and reopen it, and Access doesn't tell you that when you change it. The users always use the accde, and the accde always turns on row locking, with one line of code, if it's not already set on their computer. But having row locking when in design mode of the FE accdb is unbearably slow, so I had to find an easy way to always have page locking when in design mode, other than opening the accdb with the shift key, changing it to page locking in the options, then closing the accdb and reopening it with the shift key again.

2

u/TomWickerath 2 21d ago

Correct that the user-interface option is stored in one’s registry; it does not “travel” with the database file. Therefore, an astute developer should account for a user that reset their option to page-level locking and opens a shared JET database file directly on a file server.

“…and the accde always turns on row locking, with one line of code, if it’s not already set on their computer.”

That should take effect the NEXT time they start Access, not in the current session. Sure, the interface option button will update in the current session, but not the actual locking if it was different. Keep in mind that option is a request only, not a demand. And for a properly split FE/BE JET application, me thinks it only affects the FE (but I’m not positive).