"Incorrect string value" when inserting non-ASCII into MySQL database using ADODB in MS Access (VBA), but retry works
<p>After finding a <a href="https://stackoverflow.com/questions/40531411/output-parameter-of-mysql-stored-procedures-via-adodb-in-ms-access-vba-correct">bug in MySQL ODBC 5.3.6</a>, I have another problem which also happens using MySQL ODBC 5.3.4.</p>

<p>I have an MS Access application (Office 2016 ProPlus 32-bit) that uses ADODB with MySQL ODBC (5.3.4 32-bit) to interface to a local MySQL database server (5.7.16 64-bit) on a Windows 10 Pro 64-bit computer. Inserting non-ASCII characters in the MySQL database fails with the error "Incorrect string value" but if I try the same statement again, the SQL statement is correctly executed and the correct value is inserted into the database table!</p>

<p>To isolate the problem, I created the following table:</p>

<pre><code>  CREATE TABLE test2 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) CHARACTER SET utf8mb4,
    PRIMARY KEY (id));
</code></pre>

<p>And a test MS Access database containing the following VBA code (and a reference to the Microsoft ActiveX Data Objects 6.1 library):</p>

<pre><code>  Public Function dbTestIt2() as Long

  Dim dbConn As New ADODB.Connection
  Dim dbCmd As New ADODB.Command
  Dim dbParams As New ADODB.Parameter
  Dim l As Long

      dbConn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};option=3;database=xxx;user=yyy;password=zzz;"
      dbConn.Open
      With dbCmd
          .ActiveConnection = dbConn
          .CommandType = adCmdText
          .CommandText = " INSERT INTO test2 (name) VALUES (?);"
          dbParams.Type = adVarChar
          dbParams.Size = 100
          dbParams.Value = "abcdèfgh"
          dbParams.Direction = adParamInput
          .Parameters.Append dbParams
          .Execute l, , adExecuteNoRecords
      End With
      dbConn.Close

      dbTestIt2 = l

  End Function
</code></pre>

<p>Relevant lines in my.ini:</p>

<pre><code>  [client]
  default-character-set=utf8mb4
  [mysql]
  default-character-set=utf8mb4
  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
</code></pre>

<p>Here are the test results:</p>

<ol>
<li>Execution stops at the .Execute statement with the error 80004005: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Incorrect string value: '\xE8gh' for column 'name' at row 1;</li>
<li>If I continue debugging (F8), the SQL statement is correctly executed and the correct value is inserted into the database table;</li>
<li>If I use the "ANSI" ODBC driver instead of the "Unicode" driver, this test succeeds the first time, but then more complex (e.g. Chinese) non-ASCII characters get replaced by question marks;</li>
<li>I believe that VBA internally uses Unicode but MySQL <a href="https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html" rel="nofollow noreferrer">does not support UTF16 for clients</a> and specifying "charset=utf8mb4" in the ODBC connection string or execute "SET CHARACTER SET utf8mb4" first does not help and "SET NAMES utf8mb4" is not supported;</li>
<li>If I use INSERT IGNORE instead of INSERT, the first execution seems to succeed, but the value is actually truncated at the "è" (so only "abcd" is inserted);</li>
<li>It also happens with MySQL ODBC 5.3.6</li>
</ol>

<p>Am I doing anything wrong or is this another bug in the MySQL ODBC driver?</p>