Access rror updating a field in a table mYSql

[Gicu]

Some more info:
https://stackoverflow.com/questions/...-linked-tables

[jojowhite]

can you just run an Update Query rather than using recordset:

Dim db As DAO.Database
Dim sql As String
Dim nYear as Integer
nYear = Year(Date)
If Month(nDate) > 8 Then
    nYear = nYear + 1
End If
sql =  "Update LibroSoci Set [Quota associativa] = " & nYear & " WHERE [Numero Tessera] = " & NTes
Set db = Currentdb
With db
    .Execute sql, dbFailOnError
    Msgbox .RecordsAffected & " record(s) updated"
End With
Set db = Nothing

[roberto21]

No, I don't have any YES/NO or TRUE/False column in the table, no floating items either. And in this situation, I am not trying to update the record from a form (other suggestion found somewhere).

[Gicu]

Can you update the table manually? What about the update query suggested above?

[roberto21]

Sorry for the late answer, we have also a time sync problem ( I am in Italy).
Yes, no problem in update the table manually.
As per the code of jojo white (thank you, by the way), no luck; same error at .Execute sql instruction.

[jojowhite]

what are the field type of these two fields:

[Quota associativa]
[Numero Tessera]

If one or both are String you need to add Delimeter on your SQL string.
eg, [Numero Tessera] is string:

sql=

"Update LibroSoci Set [Quota associativa] = " & nYear & " WHERE [Numero Tessera] = '" & NTes & "'"

[roberto21]

Both [Quota associativa ] and [Numero Tessera] are defined as INT

[jojowhite]

i downloaded and install mysql 8.4.6 (x64)
created a sample database (test) and table test


i run the code i posted and the code goes well (without any error).

[Minty]

Does it not need a dbSeeChanges option added on in the SQL.Execute

SQL Server would.

[roberto21]

I had dbSeeChanges in the code using Recordset. Curiously, if I try to add dbSeeChanges to the .execute statement, like this
.Execute strSql, dbFailOnError, dbSeeChanges
I get a compile error

Compile error:
Wrong number of arguments or invalid property assignment




Jojowhite, are you suggesting that Mysql 9.3 introduced a bug not present in the earlier versions? Did you also use the old drivers? I am using odbc driver unicode 9.3. I could try using an old driver too (not sure if they are compatible with mysql 9.3), or I have to reinstall everything using the old version to test. This will require some time.

[roberto21]

All right, tested with driver 9.2,9.1, 9.0, 8.4 with same error. Will reinstall mysql 8.4.6.

[jojowhite]

i ran the code without dbSeeChanges and it runs fine.

[jojowhite]

you should run it with or without dbSeeChanges:

.Execute strSql, dbSeeChanges

[Gicu]

I didn't see the timestamp column in the post #22...
When you did the connection to the backend what options have you selected?
https://dev.mysql.com/doc/connector-...ft-access.html

[roberto21]

I removed the timestamp field, because, even inserting it, I had the same error,
But now the news, that for me are unbelievable. I installed MySQL server 8.4.6, and copied the database from the server 9.3 I had to redefine the primary keys again, must be a deficiency of dbeaver. Anyway, I relinked the frontend with driver 8.4, retested and ggot the same error.Oh well, what can I do? Just for the fun of it, I changed the .execute instruction to

db.execute strSQL, dbSeeChanges


replacing dbFailOnError with dbSeeChanges. Retested and, would you believe, NO ERROR! It worked!!! Just to make sure, I reverted to


db.execute strSQL, dbFailOnError


retested, and it worked again!!
Now I had to proceed, so I went back to the database in the server 9.3, relinked the frontend with driver 9.3, and the damn thing worked again, in exactly the same situation as before when I could not get rid of the error. If someone has an explanation of this bizarre behavior, I would like very much to learn it. The problem is that, should the error pop out again, I still do not know the cause.
Thank you all for helping me.


PS. Is there a reason why I keep getting kicked out of the forum? I had to logon five times to write this message.