When linking a SQL Server table (SQL Server 2008 R2) via ODBC, I encountered a major error where all cells in the table display #Deleted when opened from Microsoft Access 2010 (14.0.6123.5001).
The problem turns out to be that Microsoft Jet Database Engine maps bigint datatype to binary. One way to fix this is to clone the table and change the datatype from bigint to either int or (n)varchar. Changing the datatype in the original table just for this purpose is definitely not recommended.
If the table is used only as a reference then the solution is much simpler since it’s not necessary to edit or create new records. Instead of linking the table directly from MS Access, we can create a view that converts the datatype from bigint to a string (nvarchar) and link the view instead.
Create a plain view that selects all fields from the table.
CREATE VIEW dbo.vApplications
AS
SELECT CONVERT(NVARCHAR(25), Id) AS Id
,Employee
,EmpId
,CodeNumber
FROM dbo.Applications;
Then link the view from Access so we can see the data while the original table is untouched.
Further Reading
ACC2000: Linked SQL Server Table That Uses BigInt Data Type as Primary Key Displays #Deleted
