Solved

Could not update.  Currently locked By xxx

Posted on 2014-01-07
12
1,209 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
  • 7
  • 5
12 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 

Author Comment

by:damianb123
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now