Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

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
0
damianb123
Asked:
damianb123
  • 7
  • 5
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
damianb123Author Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Damian
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
damianb123Author Commented:
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.

DamianDatabase Error Screenshot
0
 
damianb123Author Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
damianb123Author Commented:
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?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
1. Create a child table with a PK field (use an autonumber), the value for the type of check (FK to your type table), and the value for the yes/no, plus a foreign key field to relate it to the main table.   You should have an additional unique index on the parent FK field and the type FK field combined.

2.  A subform based on the child table.   Only needs two controls; a combo to display the type, and then a check box for the yes/no.

3. You might want to pre-populate the child table with all possible types defaulted to no when a new parent record is added.  That's easy to do with an append query.

Jim.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now