Access: Record Locks - Edited Record property not working correctly

Question

I'm fairly new to Access and am struggling with setting a pessimistic Record Locks policy (Edited Record). I have a database that I am developing and have not published yet, so it is not split at the moment but will be in its final form. I have a few tables that will be accessed through forms by multiple users at the same time, and ideally I would want the Record Locks property of these forms to be set as Edited Record on a row basis so that once a user starts editing one record, other users cannot mess with it but can still edit all the other records. I have:

1. set the relevant forms' Record Locks property as Edited Record
2. enabled the "Open Databases by using record-level locking" advanced option
3. read/write permissions to the folder where the database is

..but it is not working correctly. It seems that Access is still locking records at the page level, as I am prevented from simultaneously editing two adjacent or "close" (within 30 rows of each other or so) records.

I even tried creating a brand new database, just one table and two forms (actually one form duplicated with two different names) - result is the same, once I start editing one row of data in one form, all "nearby" rows are locked from editing as well. I tried splitting the database, asked another user to try on his machine together with me, but always the same result. What am I possibly getting wrong?

I have no issue sharing the brand new database I made as a test if needed, not sure where I can upload it though.

Answer 1

Ok, a few things:

Given this is not split? Ok, less things to check.

so, you have this setting:

and for the test form in question, you have this:

Now, we assume you not left out some huge-massive whopper here, right?

First up - we assume no one else, or no other program has this file open.

we assume that you do NOT have ANY startup code at all - but are just launching to that form? Right (if you have startup code - especially ANY code that opens the database or opens a table? ALL BETS ARE OFF!

next up: since this is a single user machine - I would re-boot - just to ensure that no stray copes of Access are running or were left running due to all this testing.

Note that the FORM MUST open the table(s) in question. There is NOT to be ANY code of ANY kind that runs before the Access UI application say via form OPENS that table.

The above is BEYOND important!!!

Why?

Because DAO code, VB code or ANY OTHER PROGRAM or PROCESS that opens the file BEFORE YOUR access applicaton does? Then you not get row locking.

And the above tip ALSO includes ANY VBA + DAO code!!!

You see, the JET (now ACE) row locking feature? There is ONLY one way and ONE WAY ALONE to use this feature:

It ONLY works from the MS-Access UI. That means no VBA code can do this, no vb.net code or ANY OTHER system or code can run before Access UI (forms) opens a single table.

The reason why?

The first person + process WILL determine if the row locking feature is turned on. And I want to double, tripe, quadruple and MORE stress that ONLY the UI can cause the ACE/JET data engine to engage this row locking feature.

In other words: If on application startup ANY CODE that runs that opens a table (DAO or ADO code) then that code who FIRST opens the database will from that point on force ALL OTHER programs, forms and yes even MS-Access forms to NOT USE row locking. (you get page locking).

So, this is a first in kind of setting.

the FIRST process that causes a table to open will then determine if row locking is to be turned on. And the ONLY POSSBILE WAY for this to occur is if the MS-Access UI opens a table (any table) with a form. You cannot have ANY code run that touches a table BEFORE that UI opens a table. So any kind of say startup code, or even code (rather common) to create a persistent connection will thus ignore the row locking feature.

And if any other outside program, say ODBC from FoxPro, or even code in vb.net, c# etc. opens the database BEFORE the Access UI gets it hands on the table(s)? Then once again, row locking is not used (page locking of course will be used).

So the flip, the flag, the switch to row locking CAN NOT BE set in code. (you can set the options in access via code - but then you get that prompt about having to restart access (and you must do so if you change that row lock feature). So, code can change the setting, but it will NEVER take effect until you re-start. And thus once again, code cannot set this on startup (only change the settings that tells access what to do on startup - and it too late to thus change it in code once code starts running.

So, just keep in mind that you need as the first thing to launch is some form that is bound to a table, and that form will have to run first before any DAO or any other kind of VBA code grabs or opens a table via DAO or ADO code.

If code uses the DAO/ADO object model, then you by-passing the Access UI and thus no means to tell JET/ACE to use row locking. This is a MS-Access feature and NOT a JET/ACE data engine feature. As a result, you have to ensure that it is the Access UI that opens that table. Once that has occurred, then any and all other code, forms and even external ODBC connections will now respect the row locking feature.