Solved

Could not update.  Currently locked By xxx

Posted on 2014-01-07
12
1,255 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

734 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