[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Record Locking Problem

Posted on 2007-10-16
14
Medium Priority
?
534 Views
Last Modified: 2008-01-09
Error:  Could not update; current locked
(By the way I not asking about the below code, Im asking what could be causing the record locking I am seeing at many different times on many different tables.)

I have a database that I inherited from another programmer.  The other programmer was getting locking errors and I have never had locking errors on any of my databases.  So because I wasn't rewriting everything, I thought I wouldnt have any record locking with my stuff (my table  and my forms etc.)  I was wrong.

To give you an example the below piece of cod is run probably 50 times a day.  It adds a record to a table that no one ever edits, it just adds two fields and has a third field that is an auto number Id field.  Today for the first time in 3 months I got the error.  
Could not update; current locked

Set db = CurrentDb
Set RS1 = db.OpenRecordset(tbl_PhoneNbr_Lookup, dbOpenDynaset)

'------------
    RS1.AddNew
    RS1!CallLogHeaderchdr_Idd = lng_Id
    RS1!Phone = strPhone
    RS1.Update
     
'Close Table
    RS1.Close
    Set db = Nothing
    Set RS1 = Nothing
************************* end of code

Some facts about their system
      * About 6 users
      * Each user has a copy of the program database
      * The data database sits on the server
            * I think some are using Access 2003 and some are using Access 2002
      

            * They are in a different town, so the below they assure me is true, but I can personally verify it
            * In Options, Advanced Tab
                       Default record locking --> No Locks
                       A checkmark in Open databases using record-level locking


Any help would be greatly appreciated.
LJG
0
Comment
Question by:LJG
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 672 total points
ID: 20089188
Do you have code that updates the SAME table/recordset as is open on a Form? If so, that's likely where you're getting the lock message. Generally, either you update data via the form, or via a Recordset/SQL but NOT both.
0
 
LVL 2

Author Comment

by:LJG
ID: 20089301
LSM
Thanks for the response.  The answer is no.  I do not update the table in question with the exception of the code above.  The table is NEVER edited - that's the beauty of the error on this table, It shows how weird the situation is.

In theory two people could be adding a record at exactly the same time, but in the past access always handled that situation with
                * Number of Update Retries
                * Update Retry Interval

Again - Any help would be greatly appreciated.
LJG
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 664 total points
ID: 20089573
Record level locking does not apply unless you also set the default locks to Edited record.  So in fact you are running with Page locking in this case.
Obviously there is more chance of hitting a page lock than a record lock so maybe that's all it is.


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:LJG
ID: 20092165
Peter
Thanks for responding.  I thought if you set it to --> No Locks that there was no page locking.  In essence THE ONLY time you would get a locking message is
     if you and I are on the same record
    we both edit the record
     you save the record after I started editing it
     I would get a 3 button error (save, clipboard, lose my edits) when I tried to save
Last time I tried this process, it still work as explained above.
Has something changed?
Do I completely misunderstand record locking?

Thanks in advance for your help.
LJG
0
 
LVL 2

Author Comment

by:LJG
ID: 20092593
Did some research and found out that the only time
the Check on --> "Open databases using record-level locking"
has any effect is if you pick
Default record locking --> "Edited record"
If No Locks is selected, Microsoft says the above check has no effects.
http://support.microsoft.com/kb/225926
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20096234
You mean like what I said in my response?
0
 
LVL 2

Author Comment

by:LJG
ID: 20096373
Peter
Yes - Thanks for letting me know.  

The problem though is I am using optomistic locking (No Locks) and still getting the error
----> Could not update; current locked
when I added the record with the above code.  Do you have any ideas?
Thanks
LJG
0
 
LVL 2

Assisted Solution

by:ZuZuPetals
ZuZuPetals earned 664 total points
ID: 20115864
Have you tried using:
Set RS1 = CurrentDb.OpenRecordset(tbl_PhoneNbr_Lookup, dbOpenDynaset, dbAppendOnly)
?
0
 
LVL 2

Expert Comment

by:ZuZuPetals
ID: 20115866
Is the underlying datasource a link access table, or a query or...?
0
 
LVL 2

Author Comment

by:LJG
ID: 20117527
ZuZu
The undelying datasource is a linked Access table.  The problem is not so much with the code above, it's that I'm having the problem on a number of forms.  I've been programming Access since version 1 and have never seen record locking problems like this.

The main question, If you are adding a record how can there be record locking?

LJG
0
 
LVL 85
ID: 20117574
<If you are adding a record how can there be record locking>

Access locks the table anytime it needs to modify/add/delete a record. How and when it locks the table is decided by the settings you've chosen, the method you use to handle the data, etc etc. The message you're getting:

Can't Update; Currently Locked

doesn't necessarily mean someone else is adding a record; it could mean that the entire table is locked (i.e. someone has the table open in Design view, or something of that nature). It could also mean that a stray lockfile exists somewhere, and Access "thinks" the table is locked when, in fact, it is not. And finally it could mean that your datafile is corrupt, or is experiencing the beginnings of corruption. Have you tried a Compact and Repair (make a backup first)?
0
 
LVL 2

Author Comment

by:LJG
ID: 20117610
LSM
Thanks for the response.   I'm not sure what timezone you're in but it's early here!
1) This is an ongoing problem and they compact and repair the backend every morning.
2) Because we have the same problems with other tables that we are editing and adding through forms and in theory no one should be opening the table in design mode, I don't think the problem is caused by someone going to design on the table.  (They are in a multi user situation and not everybody get's the locking message at the same time.
3) I'm interested in your conversation about "Stray LockFile Exist".  Can you tell me more about that?  Why would that happen?  Is there a way to clear up the problem?

Any help or ideas would be greatly appreciated.
LJG
0
 
LVL 2

Expert Comment

by:ZuZuPetals
ID: 20117926
Some other thoughts...

Maybe one or more users is clicking "save" when entering records?  (rhough ususally in that scenario, you get the "database has been placed in a state...." error message).

I'm inclined to agree that multiple users simply adding records at the same time shouldn't be causing this problem.  Does each user have their own "client" database or are they all opening the same client database?  Have you compacted the client database?  (I like to set it to compact automatically on close, so long as it isn't large).

I have on rare occassions seen Access get into a funk with specific tables, etc.  When that's occurred I usually create a new database, import all the onjects from the problematic one, make sure all the relationships are correct, etc. then dump the original and replace it with the new one.

Is the code above all that is happening?  (is this behind a command button) or is this just a snppet of a much larger routine that is doing other analysis prior to reaching these lines of code?  Perhaps it's something in the other parts of the analysis or something else the form is doing that is not actually related to this update?

And lastly, maybe one user has a poor network connection and is frequently exiting the database in a abrupt fashion, causing the .ldb to mark their exit as suspect.  (though again, I'm not sure that sort of problem would produce this error).  Do you have LDB Viewer?  Maybe it can shed some light on it?
I couldn't immedately locate the official MS link, but did find this one:
http://www.mvps.org/access/modules/mdl0055.htm

Keep trying... you'll get to the root of it!
0
 
LVL 2

Author Comment

by:LJG
ID: 20118019
ZuZu
Thanks - I agree with help from people like you and the rest of the people who are helping I think I will get there.

Some Questions
--> User Logged in:
Your conversation about ldb file brought a thought process to my mind.  I made the statement earlier that "I have never had locking errors on any of my databases".  At the same time, this is the first database that I have been a part of that I didn't have the user log in (The old programmer had a login form).  Could the locking problem be caused by 6 or 7 people being loged in as Admin?

--> Poor Network Connection:
I suspect poor network connection, but I have almost no expertise on this.  If someone was hooked to the network and they loose network connection:
     Would that cause a locking error or would it just cause some error saying can't locate the data database?

Don't think the following are problems:
     * Each user has there own client
     * It happens even after a new update - New updates are always compacted before released
     * I already tried importing the data database on a couple of occasions - with no difference
     * The code I show is almost the entire procedure.  I'm adding a phone number to a table anytime a phone number is added or changed.  The table is NEVER edited.  The table is updated in the morning before the worker bees show up to work.(delete all records add the correct records) (The purpose is to have a lookup table so telephone software can lookup the correct record.)

I will look at your link and see if the ldb tells me anything when I get a locking error.

thanks again
LJG    
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

825 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