question

JuergKellenberger-1432 avatar image
0 Votes"
JuergKellenberger-1432 asked CData-3904 answered

ODBC connection to MySQL fails with 3146 - but only for certain tables

I am trying to establish a connection from MS Access 2019 to a MySQL database via ODBC.

I am using Windows 10 and MS Access 2019
ODBC driver from dev.mysql.com version 8.0.26 (mysql-connector-odbc-8.0.26-win32.msi)
connection to the database server through a SSH tunnel with PuTTY

Now most of it works perfectly.
ODBC test connection: OK.
I can access transparently most of the tables in MS Access. But on certain tables I tend to receive an ODBC error 3146 either immediately or after scrolling down a few pages in the datasheet. Of course, these tables are the most important ones for us :(

I cannot find anything special in these tables that do not connect properly
- they have a lot of columns, but I have other ones with more columns. One that does not work has only 8 columns with very basic datatypes.
- they have approx 20000 to 30000 rows - but I have other ones with more rows that connect perfectly
- SSH tunnel and ODBC connection are quite stable - even after such 3146 errors, I can open other tables without problems

If anyone has an idea, if there is a specific option in the ODBC driver settings that could optimize this (there are so many, I cannot check all combinations)? or another driver version?

thanks for any help on this. We would like to relieve the developers of the web platform by implementing some administrative queries and reports in MS Access. but a stable ODBC connection is of course essential for this.

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

CData-3904 avatar image
0 Votes"
CData-3904 answered

my guess is incompatible field type/property

initial suspects are date/time, long text(rich text?)...but number types could vary as well... incompatible in the sense that MySQL doesn't recognize/understand it - - possibly the data itself if not the property of the field fundamentally..

am only guessing here - haven't wrestled with it in real life..but I would dupe those tables and then with the dupe - eliminate fields and see if the connector works ok...to determine which specific field is the problem...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.