Instantly "locking" a record in multi-user Access environment

Question

So, record-locking in Access is pretty awful. I can't use the built-in record locking because it locks a "page" of records instead of just the individual records (I've tried changing the settings for using record-level locking, but it's still locking a page instead of just one record), but even if I could get that working, it wouldn't solve my issue because the record doesn't lock until the user starts to make changes in the form.

The issue is, when two people open the same record, they can start making changes and both save (thus overwriting the earlier change). To make matters worse, there are listboxes on the form that link to other tables (keyed on an ID) and the changes they make to those tables are then overwritten by any change that comes after if they both opened the same record.

Long story short, I need to make sure it's impossible for two people to even open the same record at the same time (regardless of whether or not they've made any edits to it yet).

To do this, I added a field to the table which indicates if a record has been locked by a user. When they open a form, it sets their name in the field and other users who try to open that record get a notification that it's already locked. The problem is, this "lock" isn't instantaneous. It takes a few seconds for other users to "detect" that the record is locked, so if two people try to open the same record at roughly the same time, it will allow them both to open it. I've applied a transaction to the UPDATE statement that sets the lock, but it still leaves a short window wherein the lock doesn't "take" and two people can open the same record.

So, is there a way to make an UPDATE instantaneous (so all other users immediately see its results), or better yet, a robust and comprehensive way to lock records in an Access multi-user environment?

Answer 1

It not clear why you only receiving “page” locking.

If you turn on row locking in file->options, then you ALSO need to set the particular form to lock the current record. So just turning on record locking will not help you. That setting ONLY sets the default for new forms - it is not a system wide setting.

If you correctly turn on locking for a form, then if two users are viewing the same record and one user starts to edit the record, then all others CAN NOT edit the record. Any other user attempting to edit a record will see a “lock” icon in the record selector bar (assuming record selected is turned on for the given form). They also will receive a "beep" if they try to type into any editable control on the given form.

And when they try to edit, they will see a visiable "lock" icon on the selector bar like this:

A few things:

If two users are able to edit a record, then you not turned on locking for that given form. This feature MUST be set on a form by form basis. Changing the setting in file->options->client setting ONLY SETS THE DEFAULT for NEW forms you create! So the setting ONLY applies to the default for new forms – it does NOT change existing forms.

So setting record locking is ONLY a form by form setting.

So you ALWAYS MUST set each form you want locking to the current edited record. You set this in form design, in the data tab of the properties sheet like this:

And also keep in mind that the setting of record level locking (a different setting and feature) is an access client setting and does NOT travel with the given application.

So since you state that two users can edit the same record, then CLEARY you NEVER turned on record locking for that given form. The system wide “default” record locking ONLY sets the above form default (so existing forms you have are NOT changed).

Next up: The setting of [x] Open database by using record-level locking is an Access client setting and NOT saved with the application. So this is an access wide setting, not an application setting, nor one that travels with the application.

So you have to set this on each client workstation, or you have to set this in your start-up code.

If you can’t go around and change each workstation to change this setting (or you using the access runtime), then you can use this VBA in your start-up code to set this feature:

  Application.SetOption "Use Row Level Locking", True

Note that the setting does NOT take effect until exit the application, but that’s really a “non” issue since this means the first time you run this code, some users might well be in page locking mode, and others in row locking mode. Most of the time this causes little issue.

However the next time any users launches the application then they will be in row locking mode.

I have in the past also written custom locking code. And can outline how to make this work well, but from what you posted so far, you never turned on or set locking nor had locking working correctly for any of the forms you have now anyway.

Answer 2

OK, I finally figured out all of the issues contributing to this and worked out a solution.

The problem is multi-faceted so I'll cover the issues separately:

First issue: My custom locks weren't instantaneous. Even though I was using a transaction, there were several seconds after a lock was placed where users could still access the same record at the same time. I was using CurrentDb.Execute to UPDATE the record and Workspaces(0).BeginTrans for the transaction. For some reason (despite Microsoft's assurances to the contrary from here: https://msdn.microsoft.com/en-us/library/office/ff197654.aspx) the issue was that the transaction wasn't working when using the Workspaces object. When I switched to DBEngine.BeginTrans, the lock was instantaneous and solved my immediate problem.

The irony is that I almost always use DBEngine for my transactions but went with Workspaces this time for no reason, so that was a bad move obviously.

Second issue: The reason I had to use custom locking in the first place was because record-level locking wasn't working as expected (despite being properly configured). It was still using page-level locking. This was due to a performance trick I was using from here: https://msdn.microsoft.com/en-us/library/dd942824%28v=office.12%29.aspx?f=255&MSPPError=-2147217396

The trick involves opening a connection to the database where your linked tables are contained, which speeds up linked table operations. The problem is that the OpenDatabase method is NOT compatible with record-level locking so it opens the db using page-level locking, and since the first user to open a database determines its lock level (as explained here: https://msdn.microsoft.com/en-us/library/aa189633(v=office.10).aspx), all subsequent connections were forced to page-level.

Third issue: My problem is that my forms are not just simple bound forms to a single table. They open a single record (not allowing the user to navigate) and provide several functions which allow the user make modifications which affect other records in other tables that are related to the record they're editing (through comboboxes and pop-up forms and what not). As a result, I can't allow two people to open the same record at the same time as it leaves way too many opportunities for users to walk over each others' changes. So even if I remove the OpenDatabase performance trick, I'd still have to force the Form to be Dirty as soon as they open it so the record locks immediately and no one else can open it. I don't know if this would be as instantaneous as my custom locking and haven't yet tested that aspect.

In any event, I need a record to be locked the instant a user opens it and for now I've decided to keep using my custom locking (with the fix for the transaction). If something else comes to light that makes that less than ideal, I can try removing the OpenDatabase trick and switching to Access's built-in locking and force an immediate lock on every record when it is opened.

Answer 3

You could use the method described here:

Handle concurrent update conflicts in Access silently

to handle your lock field.

Answer 4

Since Access doesn't make locking records easy, I'm wondering if you were to add a table with locked record entries whether that would solve the problem even though it would be the "duct tape, soup can and coat hanger" solution: You create a "Locked_Record" table with 2 fields a) record ID being updated and b) the user name of the person updating that record. That table would control exactly who owns and therefore can edit what record. Your form would have a search field and when the search term is entered and "Enter" pressed the form would search for the record by looking for it in the data and looking for it in the Locked_Record table. If found in the Locked_Record table, then you user gets an error saying "Record in use already" and display who owns the record. If not found in the data then the appropriate message is displayed. If found in the data and not found in the Locked_Record table, then a Locked_Record entry would be created and the user would then get the data displayed in the form. At this point nobody else can edit that record. When the user is done updating, either the user would need to press a button saying "Done updating" or the form would have to be closed. Either way the Locked_Record entry would be deleted so others could use that record. If the record owner doesn't close out the form or doesn't press the button then that is a training issue. This method could be user for multiple entities such as Customers, Employees, Departments, etc. You would just have to assure your application and DB is set up so any sub-forms used which might lock other tables would ONLY affect that record's entries in the other tables.

Answer 5

I know this is a bit old but the information here inspired me to to use the following. Basically, the me.txtApplication is a text box on the bound form. The form is bound to the table and is set to lock the edited record in the property section. This code won't do anything other than trigger that editing lock and promptly undo the change. If another user tries to load the same record it will attempt to do the same edit, trigger the error, and move to the next record or start a new record without the user being the wiser.

'Lock current record with edit-level lock by editing and removing the edit from a 
field.
'If record is already locked, move to next record.

On Error Resume Next
Me.txtApplication = Me.txtApplication & "-%$^$^$$@#$"
Me.txtApplication = Replace(Me.txtApplication, "-%$^$^$$@#$", "")

If Err.Number = -2147352567 Then
    If Me.CurrentRecord < Me.Recordset.RecordCount Then
        DoCmd.GoToRecord , , acNext
    Else
        MsgBox "No available records.", vbOKOnly, "No Records"
        DoCmd.GoToRecord , , acNewRec
        '[If the condition is not true, then we are on the last record, so don't go 
to the next one]
    End If
End If

End Sub