?
Solved

Lock Violation

Posted on 2007-07-20
22
Medium Priority
?
668 Views
Last Modified: 2013-12-12
I have a query and when I try to update the records, it gives me a warning that 291 records couldn't be updated because of lock violations?
What is this and how can I solve this problem.
I am using Access 2007
Thanks
0
Comment
Question by:syedasimmeesaq
  • 11
  • 4
  • 3
  • +2
22 Comments
 
LVL 7

Expert Comment

by:Imoutwest
ID: 19536560
What type of query are you running? Will you post the query here?

certain types of queries will lock the records, (e.g. I believe Joins, queries from multiple tables)
0
 
LVL 75
ID: 19536956
Could easily be a folder permissions issue.
Is Row Level Locking (the default) set?  Although I doubt that some 219 records could all be locked.
Is this on a network?
Is this a Front End updating in a Back End?

mx
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 19537963
Lock Violations means someone else is interfacing with the data you're trying to manipulate (i.e. someone has a Form or Table open, view the records you want to update). This is similar to a write conflict you might encounter if you and another user attempt to change the same record at the same time. Note that the "someone" could be you, if you have a form or table open ...

The fix is to insure that you have exclusive access to the database: open Access, then click File - Open, find the database and then click the down arrow immediately next to the Open button and select "Open Exclusive". Also make sure you have no other Forms, Reports, Tables, or Queries open.
0
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!

 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19543951
This is the front end that I am trying to update using access as backend. I am sure nobody else is using it while I work on it
How can I solve this problem
Thanks
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544186
I am trying to update a linked table. However when I don't make the link and just copy the table contents it updates fine. However I have to have it linked to mysql tables as it updates my website data. Please help
Thanks
0
 
LVL 75
ID: 19544204
"This is the front end that I am trying to update using access as backend"

The tables are in the front end or backend?

mx
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544215
The tables are in backend...its a mysql database and I am using ms access ODBC connection to connect to it so I can write queries and update the data that way

Thanks
0
 
LVL 75
ID: 19544240
Sorry, don't know what else I can offer.   I don't work with mysql.  

Where is this backend located?  On the web?  If so ... sure sounds like some sort of permissions issue or as LSM said .. someelse is interfacing with the data ....

mx
0
 
LVL 85
ID: 19544257
Do you have a form open when you're trying this? Or are you doing this directly from a query? If a query, can you post the SQL you have?
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544272
sure: I am doing it through the query . Here is the query

UPDATE leads INNER JOIN July_22 ON leads.phone_home = July_22.dnis SET leads.status= [July_22].[result]
WHERE (((leads.status)="Agent Error" Or (leads.status)="Operator Intercept" Or (leads.status)="No Answer" Or (leads.status)="Answering Machine" Or (leads.status)="Busy" Or (leads.status)="No Contact") AND ((leads.converted)=0));
0
 
LVL 85
ID: 19544310
So ... while this query is running, you're 100% certain that no other user has a table open, or a webpage open with the data showing, etc etc ...

Try changing your Join to a Right or Left join (probably Right, in your case) ... this probably won't make a difference, but it can't hurt.
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544316
sure I will try it... There is no table open at all. I will post in 20 minutes after I try
Thanks
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544330
Whats the difference between inner join and right or left join. It didn't work. I am surprised why would a simple update operation won't work

Thanks in advance
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 19544348
My understanding is that when you join records between tables you lock those records from being edited or updated.
0
 
LVL 85
ID: 19544401
See here for a good description of Joins:

http://www.w3schools.com/sql/sql_join.asp

ImOutWest may be on to something ... try removing your join, and doing something like this:

UPDATE leads SET leads.status= [July_22].[result]
WHERE (((leads.status)="Agent Error" Or (leads.status)="Operator Intercept" Or (leads.status)="No Answer" Or (leads.status)="Answering Machine" Or (leads.status)="Busy" Or (leads.status)="No Contact") AND ((leads.converted)=0) AND (leads.phone_home = July_22.dnis ));

0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544530
if I use this

UPDATE leads SET leads.status= [July_22].[result]
WHERE (((leads.status)="Agent Error" Or (leads.status)="Operator Intercept" Or (leads.status)="No Answer" Or (leads.status)="Answering Machine" Or (leads.status)="Busy" Or (leads.status)="No Contact") AND ((leads.converted)=0) AND (leads.phone_home = July_22.dnis ));


it pops up a prompt box asking for value of July_22.dnis

I checked the spelling of table and field and everything is there....
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544540
Now it poping up on July_22.result too

leads.status= [July_22].[result]


Thanks
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19544558
Is it because we took inner join out?
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 19549724
<Is it because we took inner join out?>

I would think so, being that when you removed the join the query doesn't know the value (or 'FROM") for the fields from the other table.
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 19550812
hmm...here is a development. When I modefied the query so that if the previos records are equal to the new results(ie the value of the field) then do not update. Now it is giving me only one or 2 violation warnings....any idea from here on?
0
 
LVL 3

Expert Comment

by:HMoellendorf
ID: 19763625
Hi syedasimmeesag,

please post the error messages as far as you can. Every error message should be posted here.

Kind regards
Henning
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 20052231
ok its not giving me any problems anymore as I found out a why it was doing it. I made more conditions available to check all the columns and fields to satisfy the conditions and then only update the one that were left.
Thanks for trying with me guys.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 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