Access linked table to SQL Server database returns #Deleted

Symptom

You have a Microsoft Access linked table that’s connected to a Microsoft SQL Server database. The compatibility level of the database is 130 (the compatibility level for SQL Server 2016) or higher, and the table that’s linked contains one or more datetime or datetime2 columns.

In this scenario, the linked table returns #Deleted in the results.

Cause

The issue occurs when the datetime or datetime2 columns contain specific fractional seconds values because the way that fractional seconds are handled for datetime2 types changed starting with SQL Server 2016. For more information about the changes, see the following articles:

Workaround

Microsoft is aware of this issue. Until the issue is resolved, use one of the following methods to work around this issue:

  • Change the compatibility level of the database to 120 (the compatibility level for SQL Server 2014) or lower.
  • Remove fractional seconds from the datetime columns.
  • Make sure that the datetime columns aren’t part of the primary key. Add a timestamp column to the table, and then use the Linked Table Manager in Access to refresh the linked table.
  • If editing data isn't requested, create a query and change the RecordsetType property to Snapshot.