MS Access Record Level Locking

BorisMatthews
BorisMatthews used Ask the Experts™
on
I have an MS Access Application running in MS Access 2003 which connects to a 2000 format database file.  I have the locking set to Edited Record level and ticked the open databases with record level locking.

However, I am still getting page level locks on tables.

I have done some research and found that this is caused by opening the database application via double clicking a shortcut or the mde file:

This article describes the problem and a work around/patch for Access 2000.
http://support.microsoft.com/kb/238258

I am using Access 2003 SP3 and if I use the work around (ie open Access first and then use file open and not double click to open) then I successfully get record level locking.

Does anyone now how to achieve record level locking AND opening an application mde file by double clicking as I really do not want users to have to open access and then open the mde file - not very user friendly!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Why are you using  record level locking?
My databases are always shared and Access warns the user when a duplicate update could destroy data.

Author

Commented:
My preference is to lock records to avoid users being confused with conflict messages.

Commented:
You can intercept these messages by code and handle them in a userfriendly way. Keep in mind that the chances for such an event are really small and when two users would do the same updates not overlapping in time, than  no one would notice and it could happen that the wrong information is stored.
Like one storing ZIPcode B and another changing ZIPcode back to A as (s)he has old information....
For this I add in my sensitive tables two fields: UpdatedBy and UpdatedOn. This will allow the user to see when the last change took place and who's responsible, enabling to ask him/her why it has been changed.

You could try to build an additional "start.mdb/.mde" that's simulating the opening of Access and the file selection from VBA code, but make sure that the .mde is executed on a PC having MS Access installed and no runtime environment.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Record level locking in Access is a bit of a misnomer.  Please refer to the KB's below for the explanation.  There is a trick you can use with ADO as the workaround if you really need record level locking.

Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60
http://support.microsoft.com/kb/306435

******
Page-Level Locking vs. Record-Level Locking
http://msdn.microsoft.com/en-us/library/aa189633(office.10).aspx 

Record-Level Locking Does Not Appear to Work
http://support.microsoft.com/kb/225926

mx
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Personally I would not bother with it.  First, as MX has pointed out, you need to open an ADO connection first as DAO was never updated to support record level locking.

Second, even though you request it, not every operation is done with record level locking (index updates, DML statements, etc).

So it's problematic at best.

Instead, if you really need record level locking for the users, then I would use the old trick of padding the records with additional data so that it occupies more then 1/2 a page (approx 1024 characters or 2048 bytes as text strings are stored in unicode).

  In doing this, you get "record level locking" because JET cannot split a record across pages, so if the first record placed is more then half a page, another record will not fit there.

  THis gives you the ability on a table by table basis decide which should have record level locking and those that don't need it.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"So it's problematic at best."
Totally agree.

And, it's relatively easy to handle Write Conflict errors should they occur.  These can be trapped in the Form Error event, and a 'friendly message' can be displayed to user, indicating what is happening and the options.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Hey Jim ... I know we always suggest the ADO trick, but have you ever actually tried that?

mx
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
@mx,

<<Hey Jim ... I know we always suggest the ADO trick, but have you ever actually tried that?

ADO or the padding?   Think you mean the padding, but just to be sure, it's no to the first and yes to the second.

There are a few wrinkles to figuring it out (the record size you need), but it does work.  All the fields except OLE and Memo are stored together on a single page and there is no mechanism for JET to split a record across multiple pages (that's when you get a "record is too large" error).

I have found however that for the most part, it's very rare to actually need record level locking and if I do, it's almost always on a "main" table, such as tblCustomers.

 The drawback is that depending on the table, it can waste a fair amount of disk space.  But usually a main table has a fair sized record to start with, so I’ve found it’s not as bad as one might think.  Also disk space today is fairly cheap almost to the point of being a non-issue.

  One other drawback that has more of an impact is that if the record contains memo or OLE fields, all bets are off.  While the “fixed” portion of the record gets locked like you would expect, OLE and Memo fields exist on LVP’s (Long Value Pages) and they are always locked at page level.  Since an LVP page can contain fields from different records on the same page, you might end up with lock conflicts regardless.

  But it does work otherwise and on the plus side, you get record level locking on a table by table basis where you need it and everything else stays with page level and efficient.

  The only other approach is of course to do your own “locking” and leave everything set for page level and optimistic locking.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Sorry, I did mean the ADO trick.

And yes, I really don't sweat record/row level locking any more, because as noted I can trap Write Conflict errors with ease.  Also, fortunately, at work here ... even across the 25 or so dbs ...  most of the users are not likely to be working on the same record.  

However, occasionally they are on the same page (no pun) ... any may encounter the write conflict message I provide.  But even that is somewhat rare, even on the one db that has 60-70 simultaneous users. But on that (and a couple of others) db, I'm using the pseudo disconnected approach, so ... only when the user hits the 'Submit' button, and a record is opened in the BE ... is there a chance for that to happen.  

Further, the total update time - to find one record out of ~ 30K records, update 8-10 fields, and close db (all in a transaction), then come back and update the same record in the local table ... is less than 0.5 seconds ... 62 mile round trip, over the WAN ( you know, that 1Gbit super fast/stable fiber optic one).  So, even with page locking, it's a VERY short time.

Even before we started archiving completed records in this db (every morning now, when today's reports are loaded), with 600K records ... it was just as fast.  Frickin amazing!
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Frickin amazing!>>

Sounds like a really solid setup!

Jim.

Author

Commented:
Thanks for all the ideas and information.  I will consider all the options and implement am appropriate strategy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial