Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old May 25th, 2006, 03:17 AM
Ian Redsell Ian Redsell is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2006
Posts: 1 Ian Redsell User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 21 m 17 sec
Reputation Power: 0
Angry Update Query Errors with Lock violation - how to troubleshoot

Dear All

I am self taught which is why I have probably missed something.

I am trying to run an active query in MS Access that updates the shared mysql database with data stored in a local table. The query errors with error xx records due to a lock violation.

I can view the query fine.

My question is how to troubleshoot this issue>
I have user access to the MYSQL database with all attributes (it is localhost)
My ODBC seems to work fine as
I can read records in no problem
I can read and write to the local table
both tables have numeric keys
I cannot see any errors in the MYSQL log
I have tried creating dummy tables on the MYSQL side and the error continues so it does not seem to be due to the local table at all.

Please help because I am spending tens of hours on trying to identify this issue and yet there is little to help on the web which makes me think it has to be an issue with my set up

Is anyone interested in a bit of extra work to help me finish this baby?

Grateful for any advice.
Thanks
Ian

Reply With Quote
  #2  
Old June 26th, 2006, 05:23 AM
iskender iskender is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jun 2006
Posts: 1 iskender User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 22 m 12 sec
Reputation Power: 0
Hi Ian,

Did you were able to solve your problem? I have exactly the same problem...

Here is a little example to clarify the situation:
- I create a simple update query to update a fields in my linked Mysql db.
If the new value is the same as the old one I get the "locked record error" for the praticular record. But the rest updates fine.

- Also when I write a script in VB basic the edit/update procedure to update the mysql table gives the "runtime error 3197".

Any comments, any one?

Reply With Quote
  #3  
Old January 14th, 2008, 04:59 PM
Aurelio Aurelio is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2008
Posts: 2 Aurelio User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 44 sec
Reputation Power: 0
old thread but old problem

Hi,
I have the same problem with the mysql odbc driver. I can edit the same tables and records via the table view. I have a primary key. I took out the timestamp because it was causing live edit problems though.
The only thing I have found is that it is record specific. I can delete all the records, append new ones and then my update query works on all records once, and only once. I can compact and repair MS access, and I still get the lock error. The problem is consistant, in that if I delete all my records and re-append, I am allowed to do one update query per record. When I go to re-update my records, I get the locked record error again. The only thing that allows me to update the records via the update query is if I delete all the records and start over.

Reply With Quote
  #4  
Old April 1st, 2008, 10:08 AM
dolfindans dolfindans is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 3 dolfindans User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 32 sec
Reputation Power: 0
Access Lock Violoations

Had a similar problem with MySQL table defined to Access via ODBC.
Could add and delete all, but had problems with conditional delete queries based on joins to other tables.

Original definition:
Field1 int(11)
Field2 int(11)
Field3 bigint(22) PK

I played around with removing the primary key - still had locking error.

Changed definition to:
Field1 int(50)
Field2 int(50)
Field3 bigint(50) PK

... and it works now.

Reply With Quote
  #5  
Old April 1st, 2008, 11:47 AM
Aurelio Aurelio is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2008
Posts: 2 Aurelio User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 44 sec
Reputation Power: 0
Very strange how just altering the size of the columns resolves a "locked record" problem. I am curious if this is a permanent fix, or if it is just fixed for now because the record-column specific lock fell off when the table was altered.
Another strange thing I noticed is the lock falls off after a day or two. I can update a single record and column once a day via an update query, and then the mysql odbc linked table gives me the error that the record is locked if I try to run the same update query on the same records. I can re-run it the next day or so though. It is as if mysql odbc creates a lock it does not drop after the update, and mysql flushes it out after so long. I've tried flushing privelages and all lock/unlock commands I could find via mysql cmd to no avail, but the lock just drops off on its own after a day or so. I will try the change datatype size though to see if it works for me.

Quote:
Originally Posted by dolfindans
Had a similar problem with MySQL table defined to Access via ODBC.
Could add and delete all, but had problems with conditional delete queries based on joins to other tables.

Original definition:
Field1 int(11)
Field2 int(11)
Field3 bigint(22) PK

I played around with removing the primary key - still had locking error.

Changed definition to:
Field1 int(50)
Field2 int(50)
Field3 bigint(50) PK

... and it works now.

Reply With Quote
  #6  
Old April 1st, 2008, 12:09 PM
dolfindans dolfindans is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 3 dolfindans User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 32 sec
Reputation Power: 0
Well, well, well.
It could've been totally coincidental....
My update logic is going to get run every night, so I'll re-post if the error crops up again in the modified table.

Reply With Quote
  #7  
Old March 18th, 2011, 08:44 AM
dolfindans dolfindans is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 3 dolfindans User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 32 sec
Reputation Power: 0
Found a better solution...

It happened again today.
And I found a better explanation on harkyman: Bottom line was to add conditions so that it would only update records when the contents weren't already the desired value.
Search for [msaccess mysql lock violation] to find the harkyman post...

Reply With Quote
  #8  
Old December 16th, 2012, 09:57 PM
pcharpe88 pcharpe88 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 1 pcharpe88 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 50 sec
Reputation Power: 0
Sounds dumb but...

My solution was to place a dummy column in every data table, to be updated with a random number by every update query. That, and do as little MySQL data management as possible through MSAccess clients.

Reply With Quote
  #9  
Old December 24th, 2012, 02:17 AM
johokessl johokessl is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 5 johokessl User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 6 sec
Reputation Power: 0
so I'll re-post if the error crops up again in the modified table.

Reply With Quote
  #10  
Old December 24th, 2012, 07:10 AM
Jess33e Jess33e is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 2 Jess33e User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m 31 sec
Reputation Power: 0
I can view the query fine.
URLURLURL
URLURL

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Update Query Errors with Lock violation - how to troubleshoot


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap