• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2398
  • Last Modified:

URGENT - Could not update currently locked error issue

Hi Experts:

I have a database I developed using Access 2003.  The data resides on a network server, and the front end contains forms, queries, etc. resides as an mde file on the users’ pc’s.  There are about 15 users that can be using the application at a given time.  There is no mdw file with the application.

There application has 4 forms.  One form is a log-on form.  When the user logs in successfully, there is a main menu to select which form to launch.  The form which is experiencing an issue is a form that uses a main form linked to a subform to input data into the database.  The data input into the main form updates Table 1 and the data input into the subform updates Table 2.  

Users are sometimes experiencing an issue and getting a message stating, “Could not update, currently locked” when trying move from the main form to the subform.  I have code to save any main form edits in the main form's lost focus event, as follows:
   
    DoCmd.RunCommand acCmdSaveRecord

I also have code in the main form's after update event to save any edits made to data on the main form, as follows:

   DoCmd.RunCommand acCmdSaveRecord
   Me.Refresh
   Forms!Frm_CaseNoteMain!CboCaseNumber.Requery

Can any expert please tell me if this code may be what's causing the issue the users are experiencing?  This code is in the form to help automatically save edits in case they don't hit the save button on the form.  The users have been told to "save often", and that seems to be helping.  Any ideas would be appreciated because I am pretty lost and frustrated with trying to solve this problem because I've tried and still not resolution.  Thanks experts.
0
azjz
Asked:
azjz
  • 15
  • 9
3 Solutions
 
FlysterCommented:
Check your forms "Record Locks" property, make sure it's set to "No Locks" and not "Edited Record".

Flyster
0
 
puppydogbuddyCommented:
Aziz,
I am sorry this wasn't resolved in your previous post.  I believe your placing the save in the lost focus event of the MainForm is is too late to prevent the conflict.  I believe the save should be placed in the on-enter event of the subform control, which occurs before the lost focus event of the MainForm . ..... see this link and hopefully it will help.

             http://office.microsoft.com/en-gb/assistance/HP051867611033.aspx
Excerpted from the link:
Working with subforms

When you open a form containing a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. The Activate event doesn't occur for subforms, however, so opening a main form triggers an Activate event only for the main form.

Similarly, when you close a form containing a subform, the subform and its records are unloaded after the form. The Deactivate event doesn't occur for subforms, so closing a main form triggers a Deactivate event only for the main form. The events for the controls, form, and subform occur in the following order:

Events for the subform's controls (such as Exit and LostFocus)
Events for the form's controls (including the subform control)
Events for the form (such as Deactivate and Close )
Events for the subform
Note   Because the events for a subform occur after the main form is closed, certain events such as canceling the closing of the main form from an event in the subform, will not occur. You may need to move these types of validations to an event on the main form.
0
 
azjzAuthor Commented:
Thanks for the response, puppydogbuddy.  Can you please tell me if you think I'd also need to put a Me.Refresh after the save command code?  I'm wondering if part of the problem with the way the code is currently is that on the lost focus event of the main form the only line of code is the save command.  Of course, if the lost focus event isn't even being executed, then it doesn't really matter what code is in the lost focus event of the main form.  

Thanks for the response Flyster.  I neglected to say the main form is set to No Locks for the Record Locking Property.  My original post had a typo.  Sorry.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
puppydogbuddyCommented:
Aziz,
The lost focus event is being executed, but too late (the subform control (which is bound to table 2 already has focus), and that is what I believe is causing your problem. That is why you need to execute your save code in an earlier event, most likely, the event prior to the on-enter event for the subform control.  AS to refresh, it should be ok if the save were executed in the  proper event.
To get a better idea of the order your events are firing, you might take a look at the tab key index. Place form in design view, then from the command menu select View> tab order.  Try to identify which control has the focus prior to the subform control, and try putting your save code in the exit event of that control.
0
 
azjzAuthor Commented:
I've done some further analysis on this.  When you enter data on the main form and then focus goes to the subform, it seems that Access is automatically saving the main form data to the database right after the main form before update event is executed.  Then the main form after update event is executed - this is the event that contains the save command.  So the order is:  1) main form before update event is executed; 2) Access automatically saves the main form data to the database; 3) the main form after update event is executed (which contains the save command).  

So my question now is if it is possible Access is trying to save the record twice and that's where the conflict arises? I'm not sure because I have code to show a message when the main form after update event executes (starts and ends), but when I look at the record when Access automatically saves it and when the save code in the main form after update event is executed, the time stamp does not change.

Any thoughts on this very thoroughly confusing situation?
0
 
puppydogbuddyCommented:
It is confusing.  The before and afterupdates you are referring to are related to controls on the form, not the form itself.  The after update event for a form occurs only if there are no enabled controls on the form.  Therefore the After update event could occur for each control being updated. ....and the first form could be dirty* at the time you open a second form.  Go to Access Help and type in Order of Events for more info. In the meantime, I am going to research on how to display via code the Orderthat events are firing as you move through your form in the normal situation.

* The form could dirtied from changes to controls via background processes as well as from  entry into a control.
0
 
puppydogbuddyCommented:
Here is a tool that may or may not help in this situation, but could prove very useful to you.  Go to this link and download the demo version and see if it helps spot object dependencies, that you were not aware of, between the two tables you are updating. Dependencies are displayed in a treeview format which makes it easy to see.  Demo version is free to test, full version costs less than $20.     http://www.aadconsulting.com/objdep.html
0
 
puppydogbuddyCommented:
Aziz,
Probably the easiest way to determine the order that events are firing in your form, is  to place the form in design view, select events, then click on some of the form events like activate, deactivate, got focus, lost focus. Then in that event procedure, place a message box that will display the name of the form and the event proc when it is fired.  For example for the MainForm lost focus event, type in:  MsgBox me.name & " lost focus."  

After you have your message boxes in place, then test your form under normal situations of use.  and jot down the order the message boxes pop up.

As for event procs on controls, I would place message boxes in the on-enter event of the subform control and the exit event of the control prior to the subform control.
0
 
puppydogbuddyCommented:
Aziz,
Here is some info, from EE expert Harfang, that I found in another post that may or may not be relevant to your situation:

Pop-up forms never get "deactivated" and never "lose focus" because they exist "outside" of the normal application windows stack (check the "window" menu). For example, you can drag them outside of the application window, they get minimized on the bottom of your sceen, etc. Even the application's toolbar buttons don't work on them.

In addition, when switching between a normal form and a popup, the normal form does not lose or get the focus...Modal forms lose the focus only when they are closed or hidden.
0
 
azjzAuthor Commented:
Thanks so much for all the ideas.  They are really appreciated.

I looked at the object dependencies within the database, but can see nothing there that would cause an issue (at least to my eyes, anyway).  

I have put message boxes in the code in the main and subform so I can try to determine what events are firing when.  Events the message boxes were placed in include activate, deactivate, got focus, lost focus, current, query, before update, after update, and current to name some of them.  I cannot see anything that would cause the issue the users are experiencing.

Can I get some feedback about removing the subform function from the form and placing it in its own form?  The new form would still be a main/subform scenario, but data entry could only occur on the subform itself.  The main form would perform look up only.  

Thanks again to all.  
0
 
puppydogbuddyCommented:
Aziz,
will get back to you asap.  In the meantime, here is a tip from www.aadconsulting.com that may be helpful:

For every Access database opened for shared use, an .ldb file is created to store computer and login names, and to place extended byte range locks. The .ldb file always has the same name as the opened .mdb and is located in the same folder.

The Jet database engine uses .ldb file information to prevent users from writing data to pages that other users have locked, and to determine who has other pages locked. If Jet detects a lock conflict with another user, it reads the .ldb file to get the computer and login name of the user who has the file or record locked.

In most lock conflict situations, Access raise a generic Write conflict message that allows you to save the record, copy it to the Clipboard, or drop the changes you made. In some circumstances, you may receive the following error message:

Couldn't lock table <table name>; currently in use by user <security name> on computer <computer name>.
1. In the form create a TextBox that sits exactly over the ComboBox

2. Set the ComboBox's Visible property to False

3. The TextBox is Visible and holds a value according to needs of the application: it may be a bound control or, as in the sample code below, unbound with the value assigned on the Form_Load event.

4. Clicking on the TextBox hides it and displays the ComboBox. After the user has updated the ComboBox, the value is assigned to the TextBox and the ComboBox hidden again.


Private Sub Form_Load()
    Me!txtHideShow.SetFocus
    Me!txtHideShow.Text = "Test"
End Sub

Private Sub txtHideShow_Click()
    Me!cmbHideShow.Value = Me.txtHideShow.Value
    Me!cmbHideShow.Visible = True
    Me!cmbHideShow.SetFocus
    Me!txtHideShow.Visible = False
End Sub

Private Sub cmbHideShow_AfterUpdate()
    Me!txtHideShow.Value = Me.cmbHideShow
    Me!txtHideShow.Visible = True
    Me!txtHideShow.SetFocus
    Me!cmbHideShow.Visible = False
End Sub

0
 
azjzAuthor Commented:
Thanks for the info puggydogbuddy.  Just some more info. The error message the users are getting is, "Could not update; currently locked."  The can update data on the main form with no problem.  The main form never locks.  Its only when the user tries to set focus on the subform (by clicking on the subform or tabbing to the subform) that they encounter the error message and the issue.  

That's why I thought separating out the two functions (the data entry function of the main form on one form by itself and the data entry function the current subform is being used for into two separate forms) may be the way to go.

The new second form would be a main form/subform design.  However, the main form data would be read only and not updateable.  The subform data would be updateable so the user can enter data or update dat
0
 
puppydogbuddyCommented:
Aziz,
You can try it, however before doing that .... something I just thought of.....
1. try entering data into the tables directly without the form and subform......if you still get the lock conflict .....it is not because of your form and subform, but some other reason.

2. Another tip I found is that if you have any boolean fields in your table, they can not be empty(null).  If you got lock conflict in #1, check if boolean fields empty then test again.

3. Also, when working with boolean in access VBA and VB.  The default for VB boolean is 0 and -1. In SQL the bit is 0 and +1.  Tinyint is 0 to 255. So smallint is the smallest data type that really handles the boolean correctly.  

4.if you are connecting to Access via ODBC/ADO, make sure cursor not set for pessimistic locking.

will get back to you.
0
 
puppydogbuddyCommented:
to pull apart  main form/subform:
1. copy and paste subform under new name
2. copy mainform(with old subform control)
3. paste under new name (subform control collapses but still exists on new main form)
4. delete subform control from new main form
5. new forms are separate and ready to be worked on
0
 
azjzAuthor Commented:
Thanks for ideas puppydogbuddy.  A couple of more things.  The database does not use a workgroup file.  This would not cause an issue would it?  Also, the data database which resides on the server has settings on the Tool, Options, Advanced tab of shared, no locks, and checked "Open using record level locking".  Could any of these settings cause a conflict?  I assume checking "Open using record level locking" means record level locking would be used rather than page level locking.

Oooh, so much frustration....  
0
 
puppydogbuddyCommented:
You have to have a system.mdw.  It is the default security file that is created when you install access for the first time.  If you did not have it, you would be geting an error message to that effect and you are not gettting such a message.  its default location is "the windows system directory".  Have a meeting, will get back to you on locking.

0
 
puppydogbuddyCommented:
In regards to your questions about locking…….go to Access command menu  and select tools>options>advanced tab, you should have “open the db in shared mode” selected and “no locks” or “edited record” selected as your default locking schema,  and “open all databases with record level locking” checked off.  Record level locking means just what it says.

With ODBC connected databases, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.

.ldb file
I believe should take a closer at this.  In most cases, Microsoft Access automatically deletes the locking information file when the last user closes the database file.  However, in cases of a lock conflict, the .ldb file may not have been properly closed….so pleased verify (without loading Access) that you have no .ldb file in your Access directory.  If the .ldb exists, you have to erase it….but may have to reboot the system before it will allow you to erase.
0
 
azjzAuthor Commented:
Thanks for the info.  I'll let you know how it goes.
0
 
puppydogbuddyCommented:
Hi azjz,
Have not heard from you.  Have you resolved your problem?  See this link :

     http://www.archive.freeola.com/m.p.access/yaaua-attempting-tctsd.shtml

it seems that direct sql code (e.g. to update a table) runs in the background and does not trigger events in access, and thus can cause a conflict with an access update query or entries made on a bound access form if not properly coordinated.
0
 
azjzAuthor Commented:
Thanks for the info.  Still working on the issue.  I've checked our network, user's pc, and nothing.  Also found this article from msdn.

http://support.microsoft.com/default.aspx?scid=kb;en-us;331594

 I am able to reproduce the error, and tried coding the MSDASQL as provider into my DAO code, still doesn't seem to help.  I also tried to separate the main and sub forms.  No answer there either.

Still looking and still frustrated but thanks.

0
 
puppydogbuddyCommented:
Azjz,
Sorry to hear that your problem is still not resolved. Question (peviously asked,never answered).....do you experience the problem if you manually enter the updates to the tables directly, without going thru the forms?  if yes, the forms and form code are not the culprits...probably either bad table structure/relations or bad data; if no, forms/form code/connection are the liikely culprit.

Other:
1. you did check the VB library references in Access and verify that there are no missing library references?
2. Have you recompiled the VBA/VB code with option explicit turned on? Any problems there?

3. Look at the data in the tables are there any duplicates? Are the table indexes working properly.....if not they should be deleted and rebuilt (takes only a few minutes). I mention this because it may be corrupt indexes that are creating the perception by Access that there is a locking conflict.
0
 
azjzAuthor Commented:
Hi puppydogbuddy:

Thanks for the additional ideas.  I looked at all of them, with the following results:

1)  I can add rows directly into the tables without going through the forms.
2) The VB reference libraries in the code database don't appear to be missing anything.
3) Option Explicit is used on all but two forms in the database, the main menu that appears after users sign in and the subform which is on the main form that the error is occurring on.  I added Option Explicit in the code on the subform in a test database and compiled, it compiled fine.
4) There's no duplicate data in the tables.  The indexes appear to be fine. (But I don't know how I'd know if the indexes weren't fine.)

Still a mystery...
0
 
azjzAuthor Commented:
Can I get an opinion on something else I found yesterday.  I was on msdn and got some information about service pack fixes for Windows XP.   The list mentioned "File Lock or Access Denied Error Message When You Save Files Over the Network."  The machines this is happening on run Windows XP.  The article states that sometimes when you try to save a file over a network (the database resides on a network server), you can have issues with locked files.  However, the error messages you would get are "Cannot Read File, File is locked," "Access denied," or "Lock Error."  What's the possibility of the two being related?
0
 
puppydogbuddyCommented:
If you are able to do the following without error as you stated in your previous post
   <<1)  I can add rows directly into the tables without going through the forms.>>>>
I would say, not likely........more likely to be missing service pack upgrades for Access and Jet.

However, because your problem is extraordinary, I would check and make sure you are current on all releases/service packs....but would start with Access, then Jet, then XP.  
0
 
puppydogbuddyCommented:
Aziz,
How are you doing??  In case you have not resolved error yet, here is another tip:

You can try to trap the error in the Form's Error event...place the following in your error handler

If DataErr = 7787 Then
    Msgbox "Write Conflict"
    Response = acDataErrContinue
Else
    'your normal error handling code
End If
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 15
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now