MySQL Bugs: #106277: error creating linked table in MS Access VBA

Bug #106277	error creating linked table in MS Access VBA
Submitted:	25 Jan 2022 12:59	Modified:	27 May 2022 7:13
Reporter:	Zoltán Szalai	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Windows (10)
Assigned to:		CPU Architecture:	x86
Tags:	ms-access, ODBC

Description:
When I try to append a new tabledef to the table collection in MS Access VBA, for a linked table, I get an error message saying "error 3191 Cannot define field more than once.". There are no duplicated fields.

It only occurs to some of the linked tables, not all of then.
(Test passed for 27 tables, failed for 5 tables.)
The same tables are working with MySQL ODBC driver version 5.3.10 (64 bit) and 8.0.26.  

Remark: some records in the table were deleted, there are gaps in the primary key.

How to repeat:
Connection string, without userid/pwd/server/schema:
"ODBC;DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=xxx;charset=UTF8;PORT=3306;DATABASE=xxx;DFLT_BIGINT_BIND_STR=1;option=4196410;user=xxx;password=xxx"

Tabledef for one of the failing tables:
"
CREATE TABLE `attendreg` (
  `idattendreg` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `workerid` int(10) unsigned NOT NULL,
  `hidref` int(10) unsigned NOT NULL,
  `attdate` date NOT NULL,
  `expired` date DEFAULT NULL,
  `editor` text COLLATE utf8_unicode_ci,
  `mystamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idattendreg`),
  KEY `hidworker_idx` (`hidref`,`workerid`),
  KEY `workerkey_idx` (`workerid`),
  KEY `cover_idx` (`workerid`,`hidref`,`attdate`),
  CONSTRAINT `hidkey` FOREIGN KEY (`hidref`) REFERENCES `docchange` (`HID`),
  CONSTRAINT `workerkey` FOREIGN KEY (`workerid`) REFERENCES `t_workers` (`boschid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=578079 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
"

VBA code failing at position "db.TableDefs.Append tdef":
?dsnless_table2("attendreg","ODBC;DRIVER={MySQL ODBC 8.0 Unicode Driver}; ...")

"
Public Function dsnless_table2(name As String, strconn As String, Optional srcname As String = "", Optional showme As Boolean = True, Optional sysme As Boolean = False)

    Dim tdef As New TableDef, sourcename As String, myconnect As String, mysource As String, db As DAO.Database
    Set db = CurrentDb
   
 On Error Resume Next
 If IsObject(db.TableDefs(name)) = True Then
 sourcename = db.TableDefs(name).SourceTableName
 DoCmd.DeleteObject acTable, name
 End If
 err.Clear
 On Error GoTo myerr
   
    db.TableDefs.Refresh
  
   'If Len(strconn) = 0 Then
   '   myconnect = connect_string
   'Else
       myconnect = strconn
   'End If
      
    If Len(srcname) = 0 Then
       If Len(sourcename) > 0 Then
          mysource = sourcename
       Else
          mysource = name
       End If
    Else
       mysource = srcname
    End If
   
    Set tdef = db.CreateTableDef(name, 0, mysource, myconnect)   
  
    db.TableDefs.Append tdef
   
 Exit Function
 Resume Next
myerr:
     Call show_ert(err.Number, erl)
   
 End Function
"

Suggested fix:
This error was not present in version 8.0.26.

Another failing linked table, easier to test:
"
CREATE TABLE `schedules` (
  `wsid` tinyint(1) NOT NULL AUTO_INCREMENT,
  `wsname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `wscomment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `editor` text COLLATE utf8_unicode_ci,
  `mystamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`wsid`),
  UNIQUE KEY `wsname_UNIQUE` (`wsname`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='work schedules - műszakrendek';
"

Hello Zoltán Szalai,

Thank you for the bug report.
Imho this is duplicate of Bug #106204, please see Bug #106204.

Regards,
Ashwini Patil

Linking a table in MsAccess via ODBC v8.0.28 raises this error.  Rolling back to 8.0.26 and problem goes away.

Thanks randall newman that fixed it for me.

I used the community installer to remove the ODBC connector and installed the older version from https://downloads.mysql.com/archives/c-odbc/

It seems to be fixed in 8.0.29. I was not able to reproduce the issue with it.
So the versions working with MS Access are 5.3.4-5.3.10, 8.0.19-8.0.26, and 8.0.29.