0

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.

emjaySX
  • 149
  • 11
  • It is not "now", It has been so for years. Also, ODBC drivers are updated., now at 18. So, stop wondering and adopt. Or, perhaps and not recommended, set the _compatibility level_ of the database to match that of the old installaion. – Gustav Aug 31 '23 at 05:37
  • If you are concerned about `SELECT *`s and other stuff, I recommend you add the linked table with PK under a different name, then make a query that doesn't select the PK under the main name. This will allow existing queries/code to function as intended, but still allows you to modify the table design, as long as you keep the query compatible with the old design. But generally, it's good to design your database in such a way that adding new columns doesn't break code, else it will become a maintenance nightmare eventually – Erik A Aug 31 '23 at 09:14
  • @Gustav, if I were in charge, things would be very different! :) – emjaySX Sep 01 '23 at 02:50
  • OK. Then return the case to management. – Gustav Sep 01 '23 at 06:13

1 Answers1

1

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

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 1
    Thanks Andre, based on your post I have been able to programmatically retrieve any existing index which exists on the TableDef, and then recreate the Index via DDL if it's lost (i.e. not mainted on the server) after refreshing the link to the new DB server – emjaySX Sep 01 '23 at 06:42