I'm a recent starter at a company where I've been employed to manage cut-over of a suite of Excel- and Access-based tools to a new environment.
These are currently interacting with SQL Server 2012, and part of the migration involves moving to SQL Server 2019.
While data retrieval is largely unaffected, the issue I'm finding is that Access (MS 365) can no longer update or delete data in linked tables as the underlying table on the DB server has no Primary Key field.
I have validated this by adding an identity field to the tables, which allows Access to modify and delete records.
Fundamentally tables should have a PK, but the question I have is why this worked in the previous environment but fails in the new environment.
Retroactively adding an identity field to the existing tables will resolve the issue with Access write/delete, but may (almost certainly will) lead to flow-on issues where 'SELECT *' queries have been used elsewhere, and I would like to be able to explain why this issue has arisen now.
When you link an ODBC table that has no PK, you get the Select Unique Record Identifier dialog box that prompts you to choose a field or fields that uniquely identify each record in the table.
If the table has such a field (or combination), you can select this and the linked table will be editable.
I assume this is how it was done in the old environment. You can open a linked table in design view and check if Access shows the "key" icon for the PK.
But beware: if the selected field(s) is not actually unique, this will wreak havoc with your data.
So it is strongly recommended to have an actual PK on SQL Server side, either a combination of existing columns or a new Identity column.
You can also set the PK in Access programmatically: https://stackoverflow.com/a/32316883/3820271