Strange 3197 error with MySQL linked tables
Miha Abrahamsberg
I have 2 tables in Access 2003 linked to a remote MySQL server. I use MyODBC
for linking.
When I try to update records using DAO.recordsets:
###code###
With rs2
.Edit
![intPD] = rs1![user_id]
.Update
End With
###
I get the 3197 MS Jet Engine error (The Microsoft Jet database engine
stopped the process because you and another user are attempting to change
the same data at the same time).
I spent a lot of time serching for a solution on the internet, but none seem
to work.
As an alternative I tried to replace the upper code with an SQL update
statement:
###code###
str = "UPDATE tblClan SET intPD =" & ID & " WHERE (ClanID =" & ID1 & ");"
DoCmd.RunSQL str
###
and this works without any problems.
WHY?? Does anyone have a fix for this?
Thank you in advance!
Miha
Alex Dybenko
try to add a timestamp field to your table, if mysql has such data type
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"Miha Abrahamsberg" <mi...@krik.si> wrote in message
news:u4E3yo41...@TK2MSFTNGP06.phx.gbl...
Tony Toews [MVP]
>###code###
>str = "UPDATE tblClan SET intPD =" & ID & " WHERE (ClanID =" & ID1 & ");"
>DoCmd.RunSQL str
>###
>
>and this works without any problems.
Note that we suggest using Currentdb.execute as this gives you error
messages and avoids the dreaded "Action query" message.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/
Miha Abrahamsberg
I have added a "timestamp" field to the table. The field has a DEFAULT value
"current_timestamp" and ON UPDATE value "current_timestamp"
Timestamp works fine (it is added with every new record and it is updated on
any update).
But my problem still remains - the 3197 MS Jet ERROR.
Anything else I could try?
Miha
"Alex Dybenko" <ale...@PLEASE.cemi.NO.rssi.SPAM.ru> je napisal v sporocilo
news:uul2gs71...@TK2MSFTNGP05.phx.gbl ...
Alex Dybenko
do you have any trigger on tblClan? If yes - then try to disable it and
check if it is a source of problem
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"Miha Abrahamsberg" <mi...@krik.si> wrote in message
news:u0A86$S3JH...@TK2MSFTNGP03.phx.gbl...
Miha Abrahamsberg
I don't have any triggers on any of the MySQL tables.
Miha
"Alex Dybenko" <ale...@PLEASE.cemi.NO.rssi.SPAM.ru> je napisal v sporocilo
news:uexEfYT3...@TK2MSFTNGP03.phx.gbl ...
Alex Dybenko
The only idea I have - that mysql timestamp field is not like sql server
timestamp, where it acts as row version number, and helps to identify
record.
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"Miha Abrahamsberg" <mi...@krik.si> wrote in message
news:ebYStec3...@TK2MSFTNGP04.phx.gbl...
Banana
Is your MySQL ODBC Driver set to return matching rows instead of default
affected rows?
Webster@discussions.microsoft.com Martyn Webster
the query will always fail with that error. If it has a different value, it
will be ok. You need to only run the query if it is actually going to change
the value. The same problem occurs with both bound forms and coded queries.
Why? There is an intentional optimisation feature of the MySQL engine. If
you make a change that is not really a change, it doesn't want to waste
resources saving the update to the database. Unfortunately, that means it
doesn't update the TIMESTAMP field either.
The ODBC driver checks that the change has been saved by checking that the
timestamp has been changed. Since it hasn't changed, it *assumes* that
someone else had the record locked so the change could not be saved.
I have tested this extensively and implemented some messy but reliable
work-arounds in our application's common interface features. They basically
compare the bound recordset fields with the form values and cancel the save
if there are no actual changes. Note that the "no change" comparison is
case-sensitive, so the problem doesn't occur if you change the case anywhere
in a text field.
It has applied to MySQL for a few years now. The MySQL "don't save
non-changes" option was originally a default option which could be turned
off, but is now an always-on "feature".
I imagine it cannot be fixed on the ODBC end, except to perhaps clarify the
message and enable it to be managed better, but perhaps ODBC locking
management can be turned off. Does anybody know?
I threw a change request to mysql.org a while ago to consider making this
feature optional again. Perhaps if the problem gets a bit more press, there
may be some more action on fixing the problem.