Rows showing as #DELETED

Question

I have rows of data in a table showing as #DELETED on one computer when using Access but they are fine in both the SQL database and on other computers using Access. It seems to be only the latest 200 rows. The Access 2007 versions and ODBC MSJet drivers look to be the same & latest on each computer. One suggestion was to change any PK or FK's to int's, but they already are.

Any ideas for a fix for this?

Seeger

Answer 1

This occurs when the tables primary key value, exceeds the range that MS Access 2010 or earlier support, 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.

Dungaree

If this is an Access issue, how would it only be affecting one user and not everyone at once? – Abarca

Difficult to call, there may be small differences in the setup, perhaps the service pack or patch levels, 32-bit vs 64, without seeing both instances separately a mystery I'm afraid. – Dungaree

Reason I ask is that I've seen this sporadically on a project I'm heading, but it never affects multiple users at once, and usually goes away on its own. Obviously I'd prefer to avoid it entirely, but if it was the 'bigint' issue that's widely reported as the cause it should be a bit more widespread, I should think. – Abarca

The main thing to note in this reply is the fact that "bigint" is something that Access can't handle, and you should cast as something else. – Philina

Answer 2

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.

Despondency

For anyone interested in this option -- when you tick the checkbox you'll get a warning that the file will no longer be openable in Access 2013 or earlier versions of Access. Also, I found that this only worked for me after I used the Linked Table Manager to relink my ODBC tables. – Cuellar

Yes, confirming Mike's comment. Great workaround without creating another view in SQL Server. But you have to relink the SQL View in the Linked Table Mgr before it works. – Rigsby

The View may be cached, so you may need to re-create the view in SQL Server if you are linking to the view rather than the underlying table. – Riedel

Answer 3

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.

Francinafrancine

Thanks for the information, I will try out this out soon. But have you seen this: #6838874 ? Not sure if decimal vs numerical is considered a great difference. – Seeger

Answer 4

This strange behavior can occur pointing Access to a SQL 2017 database (previously pointed to a SQL 2008R2 database). When we created a new DSN with an updated ODBC Driver (SQL Native Client 11), the behavior returned to normal.

Organdy

Answer 5

For me this issue started happening with the release:
Version 2204: May 17
(Build 15128.20248)

There is a reported bug in this version or even the prior version for me that is apparently caused by nvarchar primary keys. I experienced it both with this and tables without a primary key where I wasn't before. I am also using linked tables via odbc (sql native client v11).

I have seen 2 working solutions to this:

1. Rollback to the previous know working version which for me was Version 2204 (Build 15128.20224) with the command:

"C:\Program Files\Common Files\microsoft shared\ClickToRun\officec2rclient.exe" /update user updatetoversion=16.0.15128.20224

2. Install/use the ODBC Driver 17 for SQL Server instead of the native client

Related articles:

• https://www.reddit.com/r/MSAccess/comments/uxo8sm/fyi_possible_bugs_in_version_2205_build_1522520204/
• https://codekabinett.com/rdumps.php?Lang=2&targetDoc=msaccess-bug-v2205-odbc-deleted-nvarchar-primary-key

Ouellette

see also #72432494 – Three

Answer 6

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.

Blue

Answer 7

If you set a primary key on a field where SQL is using the ROW_NUMBER() function you can cast it to int. By default ROW_NUMBER() is int64 (bigInt).

Ilene

Answer 8

I was experiencing a similar issue on a form bound to a view where the underlying table had a primary key as an auto-incrementing integer. My inserts from the form was causing the record to show as #Deleted once the row was inserted.

Things I validated:

• Timestamp column on the table
• Primary Key was assigned
• Primary Key was not a bigint
• Bit columns all had defaults on SQL Server side
• Setup defaults on bound controls to bit columns for good measure

My solution was to switch the identity integer primary key to a normal integer. I then created a sequence in SQL Server (for unique values for that field) and obtained the next value of that sequence from my MS Application (in the before update event on the form) and inserted that new Id to the field.

My thought is that with the table creating the new Id on SQL Server by default, Access doesn't know that Id until it re-queries and that's why it reflects #Deleted after insert. Passing the Unique Id along with the insert resolves the issue as it removes any ambiguity around the primary key.

Apparel

Answer 9

Well just want to add solution which worked for me.

I linked some of the Views to the MS Access and those were working fine. After some time i changed type of one of the column which was Integer before and i made that VARCHAR. As this column was a Primary Key of my table (which i selected too as primary key while adding View in MS Access), it started showing "#DELETED" after that change. To resolve this issue i just re-executed same View with "ALTER VIEW" statement and used sp_refreshview 'VIEW_NAME'.

After doing this it started working for me. Hope this helps someone facing the same issue.

Joyance

Answer 10

I had the same #DELETED problem, and it was because the primary key data type was bigint . As I was querying a table created by a third-party application I was not able to modify the data type, so I created a view on the table and used CAST to convert the data type to int (after checking that the values held in the table would not cause an overflow).

Halifax

Answer 11

I had a bizarre situation where a query was returning data as #Deleted, but I would run a second time (refreshing the results within the query) and the data would be correct ... sometimes. I then wrote the query results to a table to see if that would help, and the data was actually written but the rows containing foreign keys to other tables (int(11)) were returning zeros as values. The BigInt configuration in access was set (and in the odbc driver the check for binding bigint as strings was set - out of habit from previous installations).

After much reading and confusion, I decided to uninstall my ODBC 5.03.13 driver and try an older version. I happened to have a downloaded 5.03.04 msi which I installed and my database works as expected. Bizarre solution and I have no idea why it now works, but this cost me a day of work! Figure'd I'd share it, hoping this solution might help someone in case all the other suggestions on this topic are exhausted (as I had done).

Niello

Answer 12

If you are linking a table from Views on SQL, then since ROW_NUMBER() is bigint, MS Access would show #Deleted, but if you refresh a row, then it shows you the value. To overcome this, you could make use of cast. E.g.

CAST((Row_number() OVER(ORDER BY AllColumns.AnyColumnNameSuchAsDateColumn DESC)) AS INT) AS 'id', AllColumns.* FROM (SELECT AnyColumnNameSuchAsDateColumn, ...etc FROM YourTableName WHERE YourCondition) AS AllColumns

Armond

Answer 13

What I did to solve (or workaround...) this problem was:

Private Sub Form_AfterInsert()
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
End Sub

This way, I minimally solve the question despite some sake in the GUI, because of the requery.

Lanie

Answer 14

None of the current answers worked for me. The problem tables had (non-key) datetime columns. Apparently Access has issues with fractional seconds. The resolution was to set the compatibility level back to 120 (SQL Server 2014), which fortunately I could do on this database.

Full disclosure, I got this idea from Bing Chat. I did test it and it works in my environment with Microsoft® Access® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 32-bit and SQL Server 2019.

Impassible

This sounds like the same ultimate solution as a few other answers, just via a different pathway. The outcome is basically "make SQL Server behave, support-wise, as if it is 2014 again"; the underlying cause is a difference in capabilities/support between Access and SQL. – Gresham

Answer 15

In SQL Server, if you are having "smallint" data type for Primary Key column, try changing it to "int". This solved the issue for me.

Whitton