Access 2019 #Deleted Table status (Field NVARCHAR)

[Qassimi90]

Hi,

There has been an issue regarding Access 2019 displaying #Deleted status on certain table, for only certain windows user profile.

Based on my analysis:

1. This happen to only certain user profile. If I login into the windows using another profile and open the table, the data will be normally displayed.

2. This table are linked table with SQL server 2016, so I have check and find that the table have NVARCHAR field. I try to change the field type to VARCHAR, refresh the table link, then the data can be viewed again.

Current Solution:

1. Delete the user profile and login again. Solve the problems but not sure if it will happen again in the future.

2. Installing Access Runtime. Solve the problems but at the same time i can't debug the program if there something wrong with it.

Does anyone have any idea what happen because i am really lost right now.

[#2]

@Qassimi90 

I'm having a similar issues I found this... I can't use the work arrounds he proposed but maybe your can 

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=msaccess-bug-v2205-odbc-deleted-nvarchar-primar...

[#3]

I should have noted the work around I am using is replacing the nvarchar PK with identity PK and re-adding the old nvarchar PK as a constraint. I have not tried but using a nchar as I read they are similarly effected and would cause more re-coding issues in my environment the the PK replacement. I'm also hoping Microsoft will bet even if the they do it might not be fast enough for my team.

[#4]

We try to use ODBC Driver 17 for SQL Server but nothing change. I need to find others solution. Thank for the help.

[#5]

@Qassimi90 

So to be clear.

• You installed ODBC Driver 17 on ALL users' computers.
• You relinked the tables in your Access FE to use that ODBC 17 driver.
• You distributed that updated Access FE to all users (who now have an Access accdb with links using ODBC driver 17 and they also have the ODBC 17 Driver installed) 
• The problem of #deleted fields remains for all of your users with this updated configuration?

If so, we need to know more about YOUR specific situation please.

[#6]

I can confirm that we are having a very similar issue on some machines. Afflicted machines have MS 365 Office Version 2205 Build 15225.20204 CTR and our single machine which is working normally is on 2204 (Build 15128.20248). Reviewing version notes there doesn't seem to be changes that would cause this but still it has. I changed the data format to VarChar and can view the data normally. Going to stick to that for now.

[#7]

@George_Hepworth 

• No, we tested only one of the affected computer.
• ODBC Driver 17 already available in the computer but previously we only use SQL Server Driver.
• We setup a new DSN using ODBC Driver 17, import the tables back and check them. The result is still the same #deleted fields display.
• We using .mdb, not .accdb.

HQ already request us to downgrade the Access to 2016 version. So that is our solution for now. 

[#8]

I've added your experience to the reports to Microsoft. This is particularly unsettling as it represents a different variation on the problem. We also see that other databases, such as Postgres, are being impacted the same way.

[#9]

The MS Access team is reporting that this is now fixed.

"This should be fixed now in Current Channel (Preview) channel now, and the fix should be in Current Channel tomorrow."

[#10]

Thank you.
The changes that break functionality like this are a serious concern for us. It would be good to see a detailed list for release. https://docs.microsoft.com/en-us/officeupdates/current-channel shows very little changed in the last update and nothing which suggested this problem could have been due an update.

[#11]

@Jlorbz The fact that MS has released, or will release, a specific fix confirms it is a bug that needed to be fixed.

You are not alone in questioning the process that continues to lead to problems month after month. See for example,  No Longer Set and DevHut

Others are less formal in documenting the on-going problems.

[#12]

Many thanks for those links. Very insightful! I'll keep an eye on them going forward.

[#13]

This issue seems to be a bug in the most-recent Office 365 update. I support an app that utilizes over 70 linked tables, all to a SQL Server back end. I discovered that, after the 365 update on 5/29/2022, none of the tables linked to any SQL Server table containing an nvarchar field that was part of any unique index, displayed data. I see only "#Deleted" in every cell. My issue had nothing to do with dates (datetime, smalldatetime, etc.). The nvarchar field did not have to be part of the PK. If an nvarchar field was part of any unique index, the ODBC link broke. The only solution for me was to roll back the 5/29/2022 update. After the rollback, everything went back to normal.

[#14]

Update to version 2206, which should be available already, or will be soon, depending on your update channel. Also, thanks for the additional detail on unique indexes as well as PKs being problems.

[#15]

Good morning, George -

My client is heavily dependent upon Access for a billing application with around 20 concurrent users at any one time processing over 250 million in annual revenue for 1,100 supermarkets. When Access goes down like this, it is a major disruption to the process flow as well as causing the hearts of a half-dozen people (mine included) to stop beating. I am considering advising them to disconnect the auto-update for 365 and take a more cautious approach. Your thoughts?

[#16]

Many, perhaps most, of the MS Access MVPs I know already have stopped accepted accepting auto-updates, or gone to a semi-annual channel so that they stand a chance of such bugs being fixed before they get to them. It's a trade-off between being on the "cutting edge" or the "bleeding edge", you might say. And that's a very fine line, indeed 🙂 

[#17]

George,

Thanks so much. Can you provide a link to information on the semi-annual update channel? My client has been handling this themselves and I would like to be more knowledgeable before sending them a recommendation.

Thanks again for the help.

bt

[#18]

@BillT51 

Here's the overview of Channel Updates.