Access rror updating a field in a table mYSql

[roberto21]

I am not sure if this post goes here or should go in the MySQL section, anyway this is the problem.
I have an app with access frontend and SqlServer backend (sqlexpress), that works fairly well. For the sake of learning something, I decided to try with MySQL backend, So I exported the database to MySQL using Dbeaver. No problem here, except for the fact that I had to redefine the primary key for all tables.
Now, I have a simple code to modify a field in one of the tables

Private Sub AggiornaLibroSoci(NTes As String, nDate As Date)
    Dim dbs As DAO.Database
    Dim rstLibroSoci As DAO.Recordset
    Dim strSql As String
    
    Set dbs = CurrentDb
 
    strSql = "SELECT * FROM LibroSoci WHERE [Numero Tessera] = " & NTes
    
    Set rstLibroSoci = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    
    rstLibroSoci.Edit


                If Month(nDate) > 8 Then
                                rstLibroSoci![Quota associativa] = Year(nDate) + 1
                        Else
                                rstLibroSoci![Quota associativa] = Year(nDate)
                End If
      
    rstLibroSoci.Update
  
    rstLibroSoci.Close
    Set rstLibroSoci = Nothing
 End Sub

This code works ok with sqlexpress, but it fails with mysql at the instruction rstLibroSoci.update with the message:





Useless to say that I am the only user of the database, that is hosted locally on my pc, so I don't understand what is going on here. Is there something I am missing? Is there something I shoud look into? Thank you.

[CJ_London]

Deleted - didn’t see the whole code

[Welshgasman]

So you do not need single quotes in MySQL?

strSql = "SELECT * FROM LibroSoci WHERE [Numero Tessera] = '" & NTes & "'"

which is how Access always expects?

As to multiple updates, no idea.

[Gicu]

Usually that error in MySQL is avoided by adding a timestamp field to the MySQL table (you need to refresh the link in the front-end after you do that).
Cheers,

[roberto21]

Thank you for your attention. As for the single quotes, you are probably right; string should require single quotes, but both sqlexpress and mysql do noot complaint The strange thing is that if I insert the single quotes, I get another error in the operRecordset (type mismatch). I thought "aha, of course: the table field is defined as INT, so string and INT do not match". So I redefined the column to Varchar(4). No luck: the error remained. So I removed the single quotes (note: leaving the varchar(4) in the table), and the code runs ok until the other problem.
Timestamp. I am trying to add the timestamp column using the query

ALTER TABLE librosoci ADD COLUMN TS TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

and it does not work: dbeaver runs without any message for the eternity, until I kill it.

[Gicu]

Why not simply add the timestamp column manually? I am not familiar with dBeaver, many years ago I used to use MySQL Workbench when working with MySQL backends.

[Gicu]

Here is a bit more on this:
https://dev.mysql.com/doc/connector-...ft-access.html
By the way, what ODBC driver are you using?

[roberto21]

For some reason that I have still to investigate, workbench refuses to work with mysql9.3. Is starts complaining the the connection can be established, but due to the version some functions may be unavailable, I can survive with this, but then, when I try to connect, workbench dies without saying a word. Maybe I should start all over again, reinstalling everything from scratch..

[Gicu]

Can you view the table in "design" view in dBeaver and add the column there? How did you change the data type from Int to varchar(4)? Maybe try to add a new column as int or whatever then change its data type to timestamp....

[roberto21]

Of course I can. I tried to add the column using the dialog, but then when you try to save, the query is executed to add the column. Changing the data type is trivial: you change the type in the design view, and again the query is executed to perform the actual change.
I tried to cheat as you suggest, but even adding a varchar column the behavior is the same.

Just for info, I tried also with HeidiSQL, another db manager, and the behavior is the same: the query runs forever until I kill it. So it must be a Mysql problem.

[Gicu]

Might be worth to check your permissions\privileges for your login you use to get into MySQL.

[roberto21]

All right, as I said I got rid of everything and reinstalled everything from scratch. I reinstalled mysql server 9.3, dbeaver etc. I migrated again the database from sql server to mysql and redefined the primary keys. After relinking all the tables, same error. But this time dbeaver behaved, and let me add the timestamp column without complaining or getting stuck. Unfortunately, even adding the column, no change, same error. I really don't understand: another piece of code uses the same update technique on another table, and works fine: the difference is that there I have a recordset.addnew instead of edit, followed by recordset.update.
As per permissions/privileges, I don't know: but since I create the database, I assume i have some rights over it, haven't I?

[Gicu]

Did you refresh the links from the front-end to the back-end after you added the timestamp column (using the built-in Linked Table Manager)? What ODBC driver are you using?
Cheers,

[roberto21]

Yes I did relink the tables. I am using ODBC Driver 9.3 Unicode for MySql

[Gicu]

Do you have any Yes\No fields in the table? If yes make sure they have the default set to 0 and do not allow Nulls (you will also need to run an update query in dBeaver to update any existing records with Null values to 0).
Here is a recent link with a similar issue, unfortunately no resolution found in the end but maybe you get lucky:
https://stackoverflow.com/questions/...ack-end-but-no
Cheers,