Rows keep showing #DELETED

Question

Everytime I enter data into one of the SQL linked tables, some of the rows keep saying #deleted no matter what I do. The data is fine in the actual SQL server but not within Access. I never have used Access before so I have no idea what I'm doing. Please use the most non-tech savy dialog as possible... This is all so new to me and I am not good with technology at all.

I have tried refreshing the tables by going to the "Linked Table Manager" thing but that hasn't helped. I tried completely deleting the data from both Access and the SQL Server, re-entering it into the SQL server, and creating a new linked table within Access. I have tried exporting the data into Excel from the server and importing it into Access. None of it has worked. It's only the first 10 rows of data though... The rest of the table is completely fine and all the data has similar structure so I don't know why only the first 10 rows are being affected.

Answer 1

Azure SQL Server table key fields that contain an 8203 decimal Unicode character cannot be read by Microsoft Access Version 2108 (Build 14326.21018 Click-to-Run) and cause all of the fields for the record to appear as, "#Deleted", in a Microsoft Access form or table that is linked to the Azure SQL Server table. I'm not sure how the 8203 character appeared in my database, but it appears that some people are copying and pasting the data from some other application, such as Word or Excel. In VBA, the ASC() function returns the incorrect character code 63 (question mark) for the character while the AscW() function returns the correct 8203 character code.

I fixed this problem in the table by creating a passthrough query in Microsoft Access:

update dbo.[Table] 
set dbo.[Table].[KeyField] = replace(dbo.[Table].[KeyField], nchar(8203), '') 
where dbo.[Table].[KeyField] like '%' + nchar(8203) + '%'

The ODBC parameter of the passthrough query has to be set to the parameters that are appropriate for your database.

Answer 2

Ok, there are 3 things that often cause this.

1 - Make sure the sql table has a PK column. This is often (useally) a autonumber (incrementing by 1 integer column). So when you create the column in sql server, set it as primry key (a button in the menu can be hit to set PK using the sql manager). Then change in the property sheet the column to identify "yes" and it will set the starting number (1) and the increment (1) for you. Now add the other columns.

So Access needs a PK column.

If above was not your issue, then next up that is common is if you have a "bit" column in sql eerver. These can't be null, or access goes crazy. so if you have a bit column, then MAKE sure you set the default for that in the sql table designer as (0).

If the above don't fix your issue? Then number 3 on the list is to add what is called a row version column to the sql table. Simply add a timestamp column (this is NOT a date column, but is a time stamp row).

In ALL of the above cases, after you make the change to the sql server table, you have to re-link the access table. It is sufficient to right click on the table in Access, choose linked table manager, and then check box the table in queston, and hit ok. The link will be refreshed for you.

So the above are the 3 main issues. In most cases, the PK is the issue. However, if the table on SQL also has a trigger (that inserts) to other tables, then that table trigger has to be changed - but lets take this 1 step and soluion at a time.

As a general rule, Access needs a PK column when working with sql server. If you have that, then check the null "bit" issue - sql server tables need a default setting of 0 for those columns, and if they are null, then Access don't like that.

If both above issues are NOT your issue, then adding a column of timestamp to the sql table will fix this.