MySQL Forums
Forum List  »  Connector/ODBC

mysql-connector-odbc-8.0.23 and MS Access cannot link MYSQL table with compound primary key
Posted by: Martin Pfannkuchen
Date: January 27, 2021 05:47AM

Scenario: A MYSQL database contains a table with a composite primary key (the primary key comprises 2 columns like so:

CREATE TABLE stations ( station CHAR, date DATE, PRIMARY KEY (station, date) )

On a windows computer a "user dsn" to the MYSQL database is established via the ODBC Administrator. The driver is the MYSQL ODBC connector.

In MS ACCESS (2016) the table "stations" is linked using the "user dsn" for the MYSQL database.

In MS Access, the database connection works fine. The table is linked and appears in the access database. In the design view the database fields are all correctly displayed, and the two columns "station and data" are correctly marked as primary key. However, as soon as I open the table I get the error ODBC--call failed. And all the database fields contain "#Name?".

If I "import" the table instead of link it, all data correctly downloads and can be viewed and worked on in MSAccess. If I remove the primary keys from the table in the MYSQL database, then the very same linked table correctly connects and contains all data.

Why is MS Access apparently uncapable of linking in MYSQL tables with compound/composite primary keys? Is there a solution or workaround?

Options: ReplyQuote


Subject
Written By
Posted
mysql-connector-odbc-8.0.23 and MS Access cannot link MYSQL table with compound primary key
January 27, 2021 05:47AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.