Access Multi User – Record Locking

[smarta46]

Hi.

In Access 2010 it is possible to set the default Record Locking as follow:
· File Menu
· Click Options
· Click Client Settings in the left side Options Menu Pane
· Scroll through the Client Settings until you find the AdvancedSettings
Set advanced settings as:
1. Default Open Mode – Shared
2. Default record locking – Edited Record

I would like to know:
* If when a user tries to edit a locked record the message "Write Conflict" is displayed in both case "Edited Record" and "No Locks"
* What happen exactly in each case (Error message, Program crash, ...).

I would like to receive real answers as:
* Yes or No, to the first,
* A description of what really happen, to the second.

Please anwer with concrete examples (I asked in other forums and answers where theories).

Thanks very much.

[nico5038]

Hi smarta46,

I guess your question can be answered from google links like: http://www.databasedev.co.uk/multi-user-application-record-locking.html
However, more important is wht you want to achieve.
Personally I just use the Shared option with nolocks. In general the chances that multiple users are updating the same record at the same moment will be very slim. Just imagine there's a 1 minute difference between these two users, then no one will notice this update. So I just add two fields: [LastUpdatedOn] and [LastUpdateBy] to an important table. This allows a user to see when someone else did change the record and ask this person why (s)he edited the record, when this would contradict the change (s)he made.

Idea?

Nic;o)

[smarta46]

Hi Nico.
Thanks for your reply.
Actually I am using Shared with Nolocka too.
I would like to know what happen if two users update the same record at the same momento (I know it wolud be a very Slim situation - It never happen to me):
* Will the program crash?
* Will I have a message diplayed letting me to choose what to do?
* ...?

[nico5038]

Access will issue the posted message, as there will always be a 'winner' that gets no message and a 'loser' that gets the message and is able to overwrite te change of the 'winner'. Could be a split second or CPU cycle, but one will be first.
Thus my additional fields as the winner could view the record later and wonder where his change has gone...

Nico

[smarta46]

Thanks Nico.
* After the "loser" gets the message and made his selection, the program will continue normally?
* Is it possible to choose automatically the answer (ie If "WriteConflict" Then "SaveRecord" End If)?

[nico5038]

Answer for * 1: Yes Access will continue as expected.
For * 2: Problem will be what to choose, update or not.
Personally I would leave this to the user as I guess (s)he knows best what value is the right one.

One way to control this is to use the BeforeUpdate event. First check or the form has been changed. For this you can use the "IF Me.Dirty=True" statement to check for changes and (When "dirty") then re-read the record and test the "OldValue" property of the form for fields being different as those of the re-read record. Next you can decide to update the record or skip the update.
When using the LastUpdatedOn/By fields you can message/show the user what has been changed since (s)he started changing the form.

Getting the idea ?

Nic;o)