Link to home
Start Free TrialLog in
Avatar of damianb123
damianb123Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Could not update. Currently locked By xxx

Hi,
    I have created a multi user access 2007 database, which has a front end and a back end.  On a form we have some multi value combo boxes..... now if person A opens the database from the front end software on their desktop, opens the form and opens the multivalue box and makes a change (clicks the OK button in the multivalue box) - everything is fine for them....

However, if person B now comes along and opens the front end from the link on their desktop, opens the form, opens the multi value box and makes a change, clicks OK - they receive:

Could Not Update.  Currently Locked By 'xxx' on Machine 'xxx'

If Person A goes to File -> Save, and saves the record, Person B is then fine, however if person A forgets to save and leaves their frontend open, no one else can do anything...

Any ideas anyone?

Cheers
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

That's totally normal.

You have two basic choices:

1. Switch to optimistic locking (no locks setting in the forms, which means no edit locks, not there is no locks at all).

2. Training users and/or setting the form to close after a period of inactivity.

#1 is some what of a problem because if two users edit the same record, one has the potential to overwrite the edit of the other (you get a dialog box asking what you want to do, and most save their changes).

#2 is a better approach I think.   To see how to do that, checkout this MSKB article:

How to: Detect User Idle Time or Inactivity
http://msdn.microsoft.com/en-us/library/office/ff192885.aspx

Jim.
Avatar of damianb123

ASKER

Hi Jim,
    Believe it or not, option 1 would be preferrable due to small number of users, all sat at same desk, so what would we need to do on the front or back end to set up optimistic locking?

Cheers
<<front or back end to set up optimistic locking?>>

 In the front end form and/or queries, set the lock type to "No Locks".  As I mentioned, this doesn't mean no locking is taking place, it just means no Edit locks are used.

 The difference between an Edit lock (pessimistic) and no Edit locks (optimistic) is when the lock is placed.  With the first, the lock is placed as soon as the user starts to edit the record and not released until the record is updated.

 With the second, the record is only locked while it is being updated.  While the user is editing the record, nothing is locked.

 So what you can have is this:

1. User A starts to edit the record.
2. User B starts to edit the record.
3. User A saves the record.
4. User B saves the record.

  When user B goes to save, they will get a dialog box saying that the record has changed since they started editing.

 They will have the option to either save their changes (which wipes out A's changes), discard their changes (thus loosing their work), or copying their changes to the clipboard.

  It's critical to properly train everyone how to handle this.  Without that, most often they will elect not to loose work, and then you have people saying "But I know I made that change, what happened?"

 Jim.
Hi Jim,
     I can confirm that the form and tables are already set to no locks, checked this as soon as issue started :-(

Damian
1. Can you get a screenshot of the error message.
2. WHen user A/B are editing, do either see the pencil icon in the record selector, or a circle?
3. Does this operation involve the closing of a form?  or does the form remain open and it's just record edit/saves that were talking about?

Jim.
Hi Jim,
    Apologies for my delay.  I have attached a screenshot of the error.  When I open the multi value combo box and make a change, the pencil appears, but only as the error displays on the screen, so a few seconds pass by!

In the image, you will see the multivalue dropdown box, when the OK button is clicked within here, that's when the issue occurs....

Thanks for all your assistance, and hopefully we'll get a solution - it has me puzzled.

DamianUser generated image
Jim,
    Just a quick update..... I have just tested the following:

Deleted everything on the form except the single multi value combo box, then opened another version of the front end from somewhere else - and the same issue occurs, so it is specific to being in, and making changes in the multi value combo box :-\

Damian
<<multi value combo box >>

 Ugh.   This is what I missed.   I thought you meant a multi-select box.

  The multivalue thing was something new added in Access 2007.  Behind the scenes, it's actually working with a related child table.   Before 2007, this would have taken the form of a subform in continious form view.

  So what your bumping into is page vs record locking.   User "A" is locking a page of records out in that table, and user "B" is bumping into that.

  I've never used the multivalue field type (and not many do) and I'm not sure what they (Microsoft) are doing in terms of locking.

 Let me dig a bit and see what I can come up with, but I probably won't have anything until tomorrow.

Jim.
Well, well, well, it's all documented:

Error message when two different users try to change the same record in a Microsoft Access 2007 database: "Could not update; currently locked"
http://support.microsoft.com/kb/918578

 Multivalue fields use pessmistic locking regardless of lock settings on the form.

Jim.
I'm not sure I understand their resolution either.  Doesn't seem to resolve anything, your still going to get the error and have problems.

Seems like your only choice is to drop the use of the multi value field and replace it with a child table.  Then use a subform in place of the list combo.

That will give you more control over the process and the subform will honor the nolocks setting on the child table.

Jim.
Thanks for this Jim, its a huge help.... What would we need to do with the sub form in terms of removing the combo box?
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial