Scenario:
A MYSQL database contains a table with a composite primary key (the primary key comprises 2 columns like so:
CREATE TABLE stations (
Station varchar(8) NOT NULL,
Date date NOT NULL,
PRIMARY KEY (Station,date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1.
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?
Thanks in advance,
question from:
https://stackoverflow.com/questions/65918075/ms-access-cannot-link-mysql-tables-with-compound-or-composite-keys 与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
