Reputation: 8070

Rows showing as #DELETED

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?

Upvotes: 15

Answers (15)

Uttam

Reputation: 736

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.

Upvotes: 0

Cameron Campbell

Reputation: 31

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.

Upvotes: 3

Painguin

Reputation: 1167

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.

Upvotes: 19

Matt Donnan

Reputation: 5003

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.

Upvotes: 16

Paul Evans

Reputation: 1499

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.

Upvotes: 1

José Silva

Reputation: 11

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.

Upvotes: 1

Cole W

Reputation: 15313

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:

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

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

Upvotes: 4

Michael Seltene

Reputation: 591

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

Upvotes: 1

Lee Brown

Reputation: 31

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).

Upvotes: 1

Del Lee

Reputation: 482

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.

Upvotes: 4

Travis Peterson

Reputation: 420

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).

Upvotes: 3

davebrads

Reputation: 21

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).

Upvotes: 2

Charax

Reputation: 31

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.

Upvotes: 3

K D

Reputation: 5989

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.

Upvotes: 2

eeasterly

Reputation: 774

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.

Upvotes: 6

Rows showing as #DELETED

Answers (15)

Related articles:

Related Questions