Rows showing as #DELETED

Consider the use of numeric (18,0) instead of bigint for the primary key data type in SQL. MS Access can resolve the effectively big integer PK if it is set as a numeric data type on the SQL Server side. I ran into this same issue on SQL 2008R2 with Access 2010 where all the rows displayed '#DELETED' when using a bigint PK.

This occurs when the tables primary key value, exceeds the range that MS Access supports, usually if you are using the "BigInt" type in SQL Server, if you are only looking to read the data then just create a "snap-shot" query for the table and all rows will display correctly as the "snap-shot" does not need to read all the indexes.

If you need to update the data in these rows at any time then I suggest using an ADO recordset instead.

I've been connecting Access front ends to SQL Server 2000, 2008 R2 then 2014 for years without issue. After a hard disk failure, I reinstalled SQL Server 2014 Developer on a Windows 7 (64-bit) computer and suddenly my Access 2010 forms were getting the dreaded #Deleted in every field when moving to a new record or clicking Save on the ribbon.

This was weird because an identical Windows 7 (64-bit) installation on another computer had no problems. Well, almost identical. After installing SQL Server 2014 on the new hard disk, I found only the Native Client 11.0 driver was installed, and so I modified ODBC connection strings to use DRIVER=SQL Server Native Client 11.0 in my Access VBA code. I immediately started getting the #Deleted in every field of an inserted record when using an Access form.

Investigation showed the difference between the 'good' computer that handled inserted records properly, and the 'bad' computer that got #Deleted was the presence/absence of a Native Client 10.0 driver. I downloaded the 10.0 driver from Microsoft, installed it and checked my code to insure all the ODBC connection strings used DRIVER=SQL Server Native Client 10.0.

Everything works OK now with no more #Deleted problems.

There is an option to support the BigInt data type on newer versions of Access.

File > Options > Current Database > Data Type Support Options

Apply that option and then refresh your table.