ADO .addnew with ODBC for mariadb: Error

I am working on a proof of concept to migrate a small access application to MariaDB. For the proof of concept I used the code listed in the mysql odbc developer guide: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-vb-ado.html

All the example code works fine until the following section where I get an error at "rs.Update"(cannot update record). All permissions are granted for the user.

'rs insert rs.Open "select * from my_ado", conn, adOpenDynamic, adLockOptimistic rs.AddNew rs!Name = "Monty" rs!txt = "Insert row" rs.Update rs.Close

I am using MariaDB 10.1.19 and MariaDB Connector/ODBC 2.0.16 on a Win7 64 bit platform.

Any help would be appreciated...

Edit: Last lines of odbc trace:

Valuation and B 1ad8-1f28 ENTER SQLColAttributesW HSTMT 0x004F1F30 UWORD 3 UWORD 16 PTR 0x0D267D00 SWORD 258 SWORD * 0x0018D2DC SQLLEN * 0x00000000

Valuation and B 1ad8-1f28 EXIT SQLColAttributesW with return code -1 (SQL_ERROR) HSTMT 0x004F1F30 UWORD 3 UWORD 16 PTR 0x0D267D00 SWORD 258 SWORD * 0x0018D2DC SQLLEN * 0x00000000

    DIAG [S1C00] [ma-1.0.6][10.1.19-MariaDB]Optional feature not implemented (0) 

Valuation and B 1ad8-1f28 ENTER SQLGetDiagRecW SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 1 SQLWCHAR * 0x0018D260 SQLINTEGER * 0x0018CE38 SQLWCHAR * 0x0018CE60 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE48

Valuation and B 1ad8-1f28 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 1 SQLWCHAR * 0x0018D260 [ 5] "S1C00" SQLINTEGER * 0x0018CE38 (0) SQLWCHAR * 0x0018CE60 [ 59] "[ma-1.0.6][10.1.19-MariaDB]Optional feature not implemented" SQLSMALLINT 512 SQLSMALLINT * 0x0018CE48 (59)

Valuation and B 1ad8-1f28 ENTER SQLGetDiagRecW SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 2 SQLWCHAR * 0x0018D260 SQLINTEGER * 0x0018CE38 SQLWCHAR * 0x0018CE60 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE48

Valuation and B 1ad8-1f28 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND) SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 2 SQLWCHAR * 0x0018D260 SQLINTEGER * 0x0018CE38 SQLWCHAR * 0x0018CE60 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE48

Valuation and B 1ad8-1f28 ENTER SQLColAttributesW HSTMT 0x004F1F30 UWORD 3 UWORD 23 PTR 0x0D267E10 SWORD 386 SWORD * 0x0018D2DC SQLLEN * 0x00000000

Valuation and B 1ad8-1f28 EXIT SQLColAttributesW with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 3 UWORD 23 PTR 0x0D267E10 SWORD 386 SWORD * 0x0018D2DC (12) SQLLEN * 0x00000000

Valuation and B 1ad8-1f28 ENTER SQLColAttributesW HSTMT 0x004F1F30 UWORD 3 UWORD 22 PTR 0x0D267FA0 SWORD 386 SWORD * 0x0018D2DC SQLLEN * 0x00000000

Valuation and B 1ad8-1f28 EXIT SQLColAttributesW with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 3 UWORD 22 PTR 0x0D267FA0 SWORD 386 SWORD * 0x0018D2DC (6) SQLLEN * 0x00000000

Valuation and B 1ad8-1f28 ENTER SQLColAttributesW HSTMT 0x004F1F30 UWORD 3 UWORD 13 PTR 0x00000000 SWORD 0 SWORD * 0x00000000 SQLLEN * 0x0018D2C8

Valuation and B 1ad8-1f28 EXIT SQLColAttributesW with return code -1 (SQL_ERROR) HSTMT 0x004F1F30 UWORD 3 UWORD 13 PTR 0x00000000 SWORD 0 SWORD * 0x00000000 SQLLEN * 0x0018D2C8

    DIAG [S1C00] [ma-1.0.6][10.1.19-MariaDB]Optional feature not implemented (0) 

Valuation and B 1ad8-1f28 ENTER SQLGetDiagRecW SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 1 SQLWCHAR * 0x0018D244 SQLINTEGER * 0x0018CE1C SQLWCHAR * 0x0018CE44 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE2C

Valuation and B 1ad8-1f28 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 1 SQLWCHAR * 0x0018D244 [ 5] "S1C00" SQLINTEGER * 0x0018CE1C (0) SQLWCHAR * 0x0018CE44 [ 59] "[ma-1.0.6][10.1.19-MariaDB]Optional feature not implemented" SQLSMALLINT 512 SQLSMALLINT * 0x0018CE2C (59)

Valuation and B 1ad8-1f28 ENTER SQLGetDiagRecW SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 2 SQLWCHAR * 0x0018D244 SQLINTEGER * 0x0018CE1C SQLWCHAR * 0x0018CE44 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE2C

Valuation and B 1ad8-1f28 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND) SQLSMALLINT 3 SQLHANDLE 0x004F1F30 SQLSMALLINT 2 SQLWCHAR * 0x0018D244 SQLINTEGER * 0x0018CE1C SQLWCHAR * 0x0018CE44 SQLSMALLINT 512 SQLSMALLINT * 0x0018CE2C

Valuation and B 1ad8-1f28 ENTER SQLFreeStmt HSTMT 0x004F1F30 UWORD 2

Valuation and B 1ad8-1f28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 2

Valuation and B 1ad8-1f28 ENTER SQLFreeStmt HSTMT 0x004F1F30 UWORD 0

Valuation and B 1ad8-1f28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 0

Valuation and B 1ad8-1f28 ENTER SQLFreeStmt HSTMT 0x004F3040 UWORD 1

Valuation and B 1ad8-1f28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x004F3040 UWORD 1

Valuation and B 1ad8-1f28 ENTER SQLFreeStmt HSTMT 0x004F1F30 UWORD 0

Valuation and B 1ad8-1f28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 0

Valuation and B 1ad8-1f28 ENTER SQLFreeStmt HSTMT 0x004F1F30 UWORD 1

Valuation and B 1ad8-1f28 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x004F1F30 UWORD 1

Valuation and B 1ad8-1f28 ENTER SQLDisconnect HDBC 0x004F1AC0

Valuation and B 1ad8-1f28 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS) HDBC 0x004F1AC0

Valuation and B 1ad8-1f28 ENTER SQLFreeConnect HDBC 0x004F1AC0

Valuation and B 1ad8-1f28 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 0x004F1AC0

Valuation and B 1ad8-1f28 ENTER SQLFreeEnv HENV 0x004F17B0

Valuation and B 1ad8-1f28 EXIT SQLFreeEnv with return code 0 (SQL_SUCCESS) HENV 0x004F17B0

Valuation and B 1ad8-1f28 ENTER SQLFreeEnv HENV 0x004F1828

Valuation and B 1ad8-1f28 EXIT SQLFreeEnv with return code 0 (SQL_SUCCESS) HENV 0x004F1828

I ran into the same problem. Besides some other issues, this update-error was driving me nuts, because despite of this error the first record was added to the maria-db. When i tested from scratch, i found this error only occurs, when at the time of the upadte command there are other recordsets open too. As i did not want to rewrite my access code, the first quick and dirty trial was to wrap the update command in a 'on error resume next'. That resolved the matter.

e.g.

Set rs = CurrentDb.OpenRecordset("...")
Set rs2 = CurrentDb.OpenRecordset(".....")
....
Do While Not rs.EOF
  rs2.AddNew
  rs2.Fields("...") = rs.Fields("...")
  rs2.Fields("...") = rs.Fields("...")
  ....
  ....
  On Error Resume Next
  rs2.Update
  On Error GoTo 0
Loop
....

I hope, this will work for you too. Please be also awre of potential problems with date and time fields.