When MS Access queries the table metadata, the primary index is not returned as a primary key hence it does not show up as a key column. When MS Access cannot identify a primary key, it uses unique fields as the key. Setting unique index also makes the columns unique so they are returned and then set as the key column in MS Access.
It appears MS Access defines the correct primary keys, but when all the indexes are added the wrong primary keys are defined. In the table definition there are 2 unique indexes and both are sent back to Access, but for some reason the wrong primary key is always taken. It is not known yet where the problem resides since the unique indexes are sent back to Access and seems that only one is read or used to define the primary keys in Access.
The issue is that the database table in this case Profile has different unique indexes defined for the table, one of them is for idcode column (IDCODE index) and the other for External-Source and External-ID columns (External-ID index). The IDCODE index is defined as primary key as we confirm with the customer using the Data Dictionary tool.
The ODBC driver just executes the ODBC functions sent by the client application, in this case MS Access., then using these functions the driver accesses the database and retrieve the information then is sent back to the client.
For some reason MS Access queries the information of the table, which includes column name, datatype, etc. and then using an ODBC function SQLStatistics (<https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlstatistics-function?view=sql-server-2017>) queries the index information, which doesn’t return if any of the indexes is defined as primary key. When MS Access gets the information picks the "External-ID" index as primary key, which cannot be explained since it is an internal process. It appears that MS Access expects only one unique index defined for the table.
To verify this, you can use a third party tool for example, razorSQL, which is based on ODBC too. This tool identifies the primary key correctly and looking at the ODBC log file it appears that this tool uses the ODBC function that gets the primary key information from the table, SQLPrimaryKeys (<https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprimarykeys-function?view=sql-server-2017>) while MS Access doesn’t use it.
The solution would be to have one Unique index for the Profile table since MS Access will always pick up the wrong index to define the primary key on its side. The unique index would be "idcode".