MySQL, MS Access, ODBC, Lock Violations: For The Record
Aug 1st, 2008 by admin
This probably won’t be of use to anyone who reads this website for Blender information or even a general interest in stuff that I’m working on. However, whenever I’ve solved a technical problem that apparently hasn’t yet been solved (i.e., searches turn up lots of people asking the question but no one giving a real answer), I like to document it so that anyone after me who has the same problem can find my solution. Thus: “For The Record”.
When using MS Access as a front end to a MySQL database through ODBC, updating a record can cause a warning message that the records could not be updated “due to lock violations”. It turns out that it has nothing to with locking. Access will throw this warning and disallow the update if the data you are attempting to update is the same as the incoming update.
So, with this simple query:
“UPDATE TblParameters SET Value=’1587′ WHERE Parameter=’CurrentCustomer’;”
You will get a “lock violation” if the Value field already contains “1587″.
If you’re doing this interactively, you can just click through the warning. However, if you’re getting this problem in the middle of a program or script, all you need to do is to enclose the Update command within an if/then statement that checks to see if the value you want to update is already at the desired value. If they are already the same, just skip it.
Yes, it’s one extra step, and one more hit on the DB, but that’s what you get for using Access as a front end.
Ouch, I’m glad I don’t have to use MS Access to admin a MySQL db. There are some open-source apps which work well like Navicat MySQL or there’s this
http://webyog.com/en/downloads.php
SQLyog which has a community version you could try.
Actually, I’m not admin’ing from Access. I use either the MySQL Administrator app on Mac, or just use Webmin usually. The Access part is a front end for the users, and it’s… craptastic! Actually, there are a number of gotchas I’ve discovered while having Access interface with MySQL via ODBC.
Thanks for the suggestion, though!
Ah I understand. Webmin is cool, I’ve always been terrified to dabble overly with ODBC and MySQL!
If running a script the query in vba code with a docmd.openquery or docmd.runsql, all messages can be suppressed wrapping them with a docmd.setwarnings false and docmd.setwarnings true in a script. Note however this will suppress all messages and between the false and true statements. So if something else breaks, you won’t know.
Example: this query named “qryTest” that updates a mysql table with records already in it will continue without the message:
Function UpdateSports()
DoCmd.SetWarnings False
DoCmd.OpenQuery “qryTest”, acViewNormal
DoCmd.SetWarnings True
End Function