Solved

Could not update.  Currently locked By xxx

Posted on 2014-01-07
12
1,244 Views
Last Modified: 2014-02-06
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
Comment
Question by:damianb123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 57
ID: 39763121
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
 

Author Comment

by:damianb123
ID: 39763425
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
 
LVL 57
ID: 39763490
<<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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:damianb123
ID: 39764357
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
 
LVL 57
ID: 39764829
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
 

Author Comment

by:damianb123
ID: 39766044
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
 

Author Comment

by:damianb123
ID: 39766097
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
 
LVL 57
ID: 39766210
<<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
 
LVL 57
ID: 39766218
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
 
LVL 57
ID: 39766229
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
 

Author Comment

by:damianb123
ID: 39766439
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39766602
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question