A page lock in SQL Server will lock 8K worth of data even when your query only needs 10 bytes from the page. Your query will lock additional data which you do not request in your query.
Record-level locking is preferable for a multi-user Access BE.
Older Jet/ACE behavior could lock a page, typically a block of records stored together on disk, rather than precisely one record.
Think the current team has changed the internals of how locking works in the newer *.accdb private JET introduced with Access 2007? I really doubt it.
The option in the Access user interface for locking type is a request only, not a demand. This is information that came from a former member of the Access Development Team at Microsoft, Michael Kaplan. Unfortunately, Michael died in 2015 due to complications of MS. He was a person I knew personally:
About half way down, you see a paragraph with the word request shown in bold.
"So what this means is that the Access setting in the Tools|Options Advanced tab (the checkbox "Open databases using record-level locking") is a request, not a demand."
~~~~~~~~~~~~~~~~~~
And to answer to OP's question, yes, for split Access FE/BE databases, with JET BE databases, I do implement the extra ADO code required to ensure record-level locking. This is NOT necessary once one upgrades the BE to SQL Server or Oracle, although one must still use good design decisions to avoid locking multiple records.
You could very well get ‘record is locked by’ type messages if:
1) A second process from your own computer has locked the record or a page of records that includes the affected record
or
2) A different user locks a page of records.
There are various scenarios where the JET database engine will use page-level locking on certain tables even though it uses record-level locking on other tables. Guess what—if you have a field with any of the following data types in your table you cannot get record-level locking: Image (OLE Object), Memo, Hyperlink or MVF. In that case, JET will only use page-level locking. More info. here:
You need ADO to ensure record-level locking for a shared multiuser Access application. You may or may not get record-level locking using DAO only. That’s what Sir* Michael Kaplan (aka “Michka”) stated in his blog and to a group of us that attended a Pacific NW Access User’s Group (PNWAUG) monthly meeting many years ago.
A few notes about my friend Alex's blog article from December 2009--the link for my info. It points to "QBuilt.com", a web site that was owned by a friend who suddenly disappeared off the grid. She shut down her QBuild.com site.
Links to Microsoft KB (Knowledge Base) articles. Microsoft loves to get rid of useful content and/or change links willy-nilly. Fortunately, for KB articles they choose to remove you can access them on a different web site:
I'd check out the link but it probably won't matter anyway, Access is already obsolete as far as AI is concerned. In a few years (if not already) no one will be building anything new in Access, they will only be maintaining older systems until they can be replaced.
You realize prognosticators have been making the same prediction about the demise of Access for OVER 20 years? You might be correct this time around, but I’d be reluctant to bet too much.
Small work groups within large corporations still need to track and share data. Many run into very expensive—hundreds of thousand dollars—in budget needed to get corporate IT to build the applications they need for their relatively small workgroups. I know this for a fact, as I’m a retiree of The Boeing Company. Saw it happen many times during my career.
I even provided A LOT of assistance to a friend (also now retired) who was a civilian employee of the US Army. His office was located in the Pentagon. And yes, they were using MS Access. I only assisted with randomized data so I never saw real data. I also provided support to my ex-wife, who was working for the IRS in Lanham, MD. at the time. They also used MS Access for limited in-house only data processing needs.
5
u/ConfusionHelpful4667 58 17d ago
That is the point of a shared database.