Record Locking Dilemma

[dgunner71]

Hello once again -

I have a table - tblPurchaseOrders.

When I access/dirty a record from tblPurchaseOrders via my form, frmPurchaseOrers, MS Access is locking 5 or 6 records in the vicinity of the record being edited. (If I open the tables and use the keys to move through records, I am getting a circle with a line through it for six records -- I am the only user in the system when this is happening). Sometimes, 6 records are affected, sometimes only 2.

I'm using MS Access 2013. I have the form Record Locks set to 'Edited Record'

I'm attaching a snapshot of the advanced settings from the Access Options - Client Settings, Advanced screen.

This issue is wreaking unpredictable havoc in a system of mine - any assistance would be greatly appreciated.

Thank you.

Gunner

[NeoPa]

Sorry Gunner. That's about as precise as you get I'm pretty sure. I certainly would have expected record locks to be single records with record level locking, but it seems to be not so from your description :-(

[dgunner71]

NeoPa,

Thanks for your reply. I did read that the advanced settings screen above is only for newly created forms, but as I noted, this form is indeed set to Edited Record.

If I set the form to 'No Locks', this appears to alleviates the situation. I am using a field called [txtRecordOpenStatus] that is updated in the OnLoad Event to 'Open - UserID'. If a user tries to open the record, the user is informed that UserID has the record open. I guess I can fall back on this solution (i.e. No Locks) but I rather not - I've always read that it is bad.

Any additional advice on this or on using 'No Locks' would be greatly appreciated.

Kind regards.

Gunner

[dgunner71]

As an update, msdn.microsoft notes the following:


Edited Record (2) Property Setting -

(Forms and queries only) A page of records is locked as soon as any user starts editing any field in the record and stays locked until the user moves to another record. Consequently, a record can be edited by only one user at a time. This is also called "pessimistic" locking.

Does anyone have any idea what a 'Page of Records' entails?

Thanks in advance for any insight.

Kind regards.

Gunner

[dgunner71]

A few more pieces of this 'puzzle':

This is the exact issue I'm referring to - When I open a record, several of the neighboring records are locked along with the edited record. It seems like if I open a record in the middle of a table, 6 (seems to always be 6) records are locked. If I open the last record in the table, it seems like only 2 records are locked.
http://www.access-programmers.co.uk/...d.php?t=115666


Regarding the size of a page (this was related to SQL Server, but... from StackOverflow)

Page Lock

A page lock in SQL Server will lock 8K worth of data even when your query only needs 10 bytes from the page. So your query will lock additional data which you do not request in your query.

Have a good evening.

Gunner

[zmbd]

----------

help access run faster (acc2007)

change the page-level or record-level locking setting
access locks a certain amount of data while you edit records. The amount of data that is locked depends on the locking setting that you choose. You can help improve performance by choosing page-level locking. However, page-level locking may decrease data availability, because more data is locked than with record-level locking.

Page-level locking access locks the page that contains the record (the page is the area of memory where the record is located). Editing a record with page-level locking enabled might also cause other records stored nearby in memory to be locked. However, performance is generally faster when you use page-level locking instead of record-level locking.
Record-level locking access locks only the record that is being edited. Other records are not affected.
Change the page-level or record-level locking setting

1.open the database that you want to adjust.
2.click the microsoft office button , and then click access options.
3.in the left pane, click advanced.
4.in the right pane, in the advanced section, select or clear the open databases by using record-level locking check box.
Choose an appropriate record-locking setting
access locks records while you edit them. The number of records that access locks and the amount of time those records are locked depends on the record-locking setting that you choose.

No locks access doesn't lock a record or page until a user saves changes to it, which results in data being more readily available. However, data conflicts (simultaneous changes being made to the same record) can occur if you use this setting. When a data conflict occurs, the user must decide which version of the data to keep. This is generally the fastest option, but data conflicts may outweigh the performance gain.
Edited record access locks a record as soon as a user begins to edit it. As a result, records are locked for longer periods of time, but data conflicts are less likely.
All records access locks all the records in a table while any form or datasheet that uses that table is open. This can improve performance for the user who is editing data in the table, but it restricts other users' ability to edit data more than the other options do.
Change the record-locking setting

1.open the database that you want to adjust.
2.click the microsoft office button , and then click access options.
3.in the left pane, click advanced.
4.in the right pane, in the advanced section, under default record locking, click the option that you want.

----------

[NeoPa]

I guess you should now have a better understanding of all the issues Gunner.

Z's last post expresses exactly what I was thinking. I would have expected multiple records to be locked, when you choose to lock just the edited record, only when the DB is opened with record-level locking cleared. As your graphic indicates this not to be the case I'm frankly confused.

While queries and forms can make their own locking settings as far as All / None / Record is concerned, I'm not aware they can choose to use non record-level for a database where that is set - which appears to be indicated here from your description.

[dgunner71]

Thanks for your replies, NeoPa and ZMBD.

That article was a great insight and, as NeoPa notes, highlights the crux of my dilemma. MS Access 2013 Record-level locking does not seem to be working (i.e. irrespective of the setting, multiple records are locked when editing a record).

I tried the following last night:
Unchecking record level locking. (same result)
Basing the form on a query. (same result)
Opening MS Access and navigating to the file and opening. (based on another article - same result)
Setting the form to 'No Locks'. (fixes issue but less than ideal solution)
Running the db on a different computer. (same result)

I'll keep searching for a solution and update this post if I find something.

Thanks again to all!

Gunner

[NeoPa]

From the 2010 help page :

Access 2010 Help:

Open databases by using record-level locking

Makes record-level locking the default for the currently open database. Clearing this check box makes page-level locking the default for the open database. The choice you make applies to data in forms, datasheets, and code that uses a recordset object to loop through records. This option does not apply to action queries or code that performs bulk operations by using SQL statements.

I believe this explains why some situations lock more records than expected, though in my testing I've still found multiple records locked even after closing and re-opening the database after this was set to True and dirtying a single record on a form.

[NeoPa]

Actually, I think the following page (Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60) explains the real problem better. It simply doesn't work in most cases.

The link also provides a work-around with some sample code if you believe it's important to have this for your project. It's what i'll be working on for one of my projects very shortly as it happens.

[dgunner71]

I'm under the impression that setting this master-level function only applies to newly created objects and can simply be over-ridden in the properties.

I have given up on finding a solution to this and have chalked this up (as NeoPa notes) to a Jet issue (which exceeds my knowledge by leaps). I appreciate the feedback / insight, though.

Gunner

[NeoPa]

DGunner71:
I'm under the impression that setting this master-level function only applies to newly created objects and can simply be over-ridden in the properties.

This is true for the Default record locking part certainly. Only that part though.

DGunner71:
I have given up on finding a solution to this and have chalked this up (as NeoPa notes) to a Jet issue

There is a solution, and I intend to work on one for my own project, but it couldn't be said to be straightforward. I may post something later to help others in similar positions but it certainly won't be for everyone.

[CRSjim]

Having only 10 people adding and updating data from an MsAccess 2003 (or 2007) form, we get record locks on not only new records but changing old records (that aren't near each other)!
Only solution found was to turn off Record Locking on the form properties and like magic - no more locked forms due to record locking! Hope that helps a few people out there (not ideal but Access does pop up a message on "real" same-record locking).

[NeoPa]

According to the link from my post #10, Access 2007, which uses ACE instead of Jet, has the later version of DAO by reason of that being inbuilt in ACE.

According to that logic, I would expect Access 2007 to work as expected as long as the options are correctly set.

By following the advice in that article I was able to get working record-level locking in Access 2003 - even though it uses DAO 3.60. The work-around is explained in the article and uses ADODB. Checking is not too straightforward as whoever opens the database first sets the option for all others.

I suggest you read the linked article carefully before determining it isn't working for you. It certainly did for me when it was set up correctly and all circumstances were taken into consideration as explained in the article.