Expert guide

#Deleted in Access Linked Tables (SQL Server): Causes and Fixes

The rows are usually still there. Access just cannot find them by their key.

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: #Deleted appears when Access cannot re-locate a SQL Server row by the key it uses. Give every linked table a primary key of an exact type (int or bigint identity, not float or imprecise datetime), enable BigInt support and relink, define a unique index when linking views, add a rowversion column to edited tables, and stop triggers from changing keys. The data is almost never actually gone. These fixes are part of every Access to SQL Server migration we deliver.

Rows full of #Deleted in a datasheet look alarming, especially right after moving tables to SQL Server. In nearly every case nothing has been deleted. Access works with linked tables by fetching keys first and then the rows that belong to them. When the key it holds no longer matches anything it can find on the server, it marks the row as deleted.

How Access Tracks Linked Rows

For a linked ODBC table, Access uses the primary key, or the unique index chosen at link time, as a bookmark. It fetches a list of keys, then requests rows by those keys as you scroll. After you save a new record, it asks the server for the new row by its key. Any mismatch in that round trip produces #Deleted.

Causes and Fixes

CauseWhat happensFix
No primary key on the SQL tableAccess cannot identify rows; table may be read-only or show #DeletedAdd an identity primary key; relink
Float / real keyRounding makes the key value differ slightlyUse int, bigint or decimal keys
datetime2 or datetime key with fractionsPrecision lost in transferAvoid date keys; add an identity key
bigint without BigInt supportKey treated as text in older setupsEnable BigInt support in options and relink
Linked view without unique indexAccess guesses or cannot track rowsChoose a unique identifier when linking, or create a unique index on the linked view
Triggers changing the key or identityAccess looks for the wrong new row after insertDo not change keys in triggers; use SCOPE_IDENTITY in trigger logic
Default values computed on the serverKey not known to Access at save timeLet the identity generate the key; avoid computed keys

Fixing Linked Views

-- Run in Access (SQL view of a query, or CurrentDb.Execute) after linking the view.
-- This creates a local "pseudo index" that tells Access which fields are unique.
CREATE UNIQUE INDEX ixPK ON [dbo_vwOpenOrders] (OrderID);

The index is stored in the Access front end, not on SQL Server. If you relink the view by deleting and recreating the link, recreate the index too; a relink routine can do it automatically.

Add a rowversion Column

ALTER TABLE dbo.Orders ADD RowVer rowversion;

With a rowversion (timestamp) column, Access checks a single value to detect whether a row changed, instead of comparing every field. That reduces write conflicts and some #Deleted and "record has been changed by another user" problems, especially on tables with floating-point or long text columns. Relink after adding it.

Triggers and Identity

  • If an insert trigger writes into another table that has its own identity column, older patterns relying on @@IDENTITY may return the wrong value.

  • Inside your own server code, use SCOPE_IDENTITY() for the identity created in the current scope.

  • Never update the primary key of the inserted row inside a trigger.

  • Test by inserting through the Access form and confirming the new row displays immediately, not as #Deleted.

Enable BigInt Support

  • File → Options → Current Database → Data Type Support Options → tick Support Bigint Data Type for Linked/Imported Tables.

  • Relink all affected tables.

  • Note: once enabled, older Access versions without Large Number support cannot open the file.

Quick Diagnostic Steps

  • Open the SQL table in SSMS: does it have a primary key, and what type is it?

  • In Access, open the linked table in Design View: is a key shown?

  • Does #Deleted appear on open, or only after inserting? On insert points to identity or trigger issues.

  • Is the object a view? Check for a unique index on the linked view.

  • Do the rows reappear after closing and reopening? Then it is a key-tracking problem, not data loss.

If saves also fail with "ODBC--call failed", read ODBC--call failed in Access. Keeping links consistent across PCs is covered in relinking Access tables with VBA, and planning a migration that avoids these problems in AI-assisted Access to SQL Server migration.

FAQ

#Deleted in linked tables FAQ

Keys, views, triggers and BigInt

Access identifies each row by its primary key (or the unique index you chose when linking). If it cannot re-find a row by that key after fetching or saving it, it shows #Deleted. Common causes are tables without a primary key, keys Access cannot represent exactly (float, some datetime values, bigint without BigInt support enabled), and triggers or defaults that change key values on insert.

Usually not. The data is still in SQL Server; Access simply cannot match its cached key to the server row. Refreshing or reopening the table often shows the row again, which is a clear sign of a key problem rather than real deletion.

When linking a view, Access asks you to choose a unique record identifier. Choose fields that are genuinely unique and of exact data types. You can also create the index afterwards with a CREATE UNIQUE INDEX statement against the linked view in Access. Without one, the view is read-only or shows #Deleted.

In older setups, yes, because Access converted bigint to text. Current Access versions support the Large Number data type; enable 'Support Bigint Data Type for Linked/Imported Tables' in the current database options and relink the tables.

Yes. If a trigger or default changes the key, or inserts into another table and changes @@IDENTITY, Access may look for the wrong row after saving. Use identity keys, avoid changing key values in triggers, and make sure triggers use SCOPE_IDENTITY rather than affecting the identity Access reads.