We use an ODBC connection to bring some tables from a vendor supplied database into MS Access so that users can create their own on demand queries without needing to know SQL. Some of those tables, not all of them, have started to have problems in MS Access so that every field is populated with #Deleted when we try to open the table. We can query these tables fine using pl/sql Developer. 3 tables that are working have num datatypes for primary key(s). 3 tables that are not working use a combination of num and char datatypes as the primary keys. No bigint datatypes in these tables. There are no pending deletes on the 3 tables that are not working. Since the tables are vendor delivered, we can't change the data structure. A combination of Oracle driver 12.01.00.02 or 12.02.00.01 and MS Access version 2112 build (14729.20194) - the 3 problem tables all show #Deleted in all fields. A combination of Oracle driver 12.02.00.01 and MS Access version 2111 build (14701.20240) - the 3 problem tables work properly and show the data in all fields. This seems to indicate that the problem is with the version of MS Access. I've been looking for other reports of #Deleted and what I can find are from 6 or 7 years ago and not very many are recent reports. Does anyone have an idea for a workaround? We hate to lose the ability for our users to utilize MS Access for querying these tables.

Someone else has posted that the later SQL ODBC Driver Version 17 doesn't suffer from this. See this thread for more details : https://www.access-programmers.co.uk/forums/threads/new-access-sql-backend-bug-deleted-showing-in-tables.323388/ That would also explain why we have only seen it on older legacy systems.

@Minty_99 thanks for the reply! We were only seeing the issue with users who were on a higher version of O365 Access, but couldn't wait for an update to fix the update. Hopefully these posts will helps others. I agree with you, I am regularly not impressed with how things are pushed out by MS without adequate notice.

We are experiencing this now on two different client systems, that had been working solidly for a number of years. (Azure backend data) In both cases so far, the Primary key on the affected table has been an nvarchar(), these have both been legacy systems adapted to Azure BE data storage otherwise they would have a separate numeric PK field. When we open the same database FE, with ostensibly the same versions of Access, we don't see the issue. In both cases changing the field(s) type to varchar has fixed the issue, but this definitely appears to be a "silently distributed" Microsoft introduced bug. This is the third or fourth bug we have seen in as many months with Access, I'm not impressed. Edit - after some tests Access version 2204 does not suffer this. Access version 2205 does :

There seems to be multiple causes to this issue: ID int fields, nvarchar in the PK and/or datetime fields. When I was researching tried everything and the only update that worked for me was changing the nvarchar fields in the key to varchar. Having nvarchar in other fields on the table did not matter. That issues wasn't reported in the MS doc - as far as I could find - but it was the common element to our tables having the issue (only 4 of 10) in O365 Access linked to a SQL Server 2016 db (in 130 compat mode).

The issue is to create an ID in MS SQL which MS Access does not understand. E.g. I created an ID as bigint and got this issue...after changing it to int it worked...

MS Access Displays #Deleted in Every Field for Certain Tables

Anonymous

We use an ODBC connection to bring some tables from a vendor supplied database into MS Access so that users can create their own on demand queries without needing to know SQL. Some of those tables, not all of them, have started to have problems in MS Access so that every field is populated with #Deleted when we try to open the table. We can query these tables fine using pl/sql Developer.

3 tables that are working have num datatypes for primary key(s).
3 tables that are not working use a combination of num and char datatypes as the primary keys. No bigint datatypes in these tables.
There are no pending deletes on the 3 tables that are not working.
Since the tables are vendor delivered, we can't change the data structure.
A combination of Oracle driver 12.01.00.02 or 12.02.00.01 and MS Access version 2112 build (14729.20194) - the 3 problem tables all show #Deleted in all fields.
A combination of Oracle driver 12.02.00.01 and MS Access version 2111 build (14701.20240) - the 3 problem tables work properly and show the data in all fields.

This seems to indicate that the problem is with the version of MS Access. I've been looking for other reports of #Deleted and what I can find are from 6 or 7 years ago and not very many are recent reports. Does anyone have an idea for a workaround? We hate to lose the ability for our users to utilize MS Access for querying these tables.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

54 answers

Anonymous

2022-05-26T15:48:13+00:00

Someone else has posted that the later SQL ODBC Driver Version 17 doesn't suffer from this.
See this thread for more details : https://www.access-programmers.co.uk/forums/threads/new-access-sql-backend-bug-deleted-showing-in-tables.323388/

That would also explain why we have only seen it on older legacy systems.
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments
Anonymous

2022-05-26T12:27:59+00:00

@Minty_99 thanks for the reply!

We were only seeing the issue with users who were on a higher version of O365 Access, but couldn't wait for an update to fix the update.

Hopefully these posts will helps others.

I agree with you, I am regularly not impressed with how things are pushed out by MS without adequate notice.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2022-05-26T11:11:58+00:00

We are experiencing this now on two different client systems, that had been working solidly for a number of years. (Azure backend data)

In both cases so far, the Primary key on the affected table has been an nvarchar(), these have both been legacy systems adapted to Azure BE data storage otherwise they would have a separate numeric PK field.

When we open the same database FE, with ostensibly the same versions of Access, we don't see the issue.

In both cases changing the field(s) type to varchar has fixed the issue, but this definitely appears to be a "silently distributed" Microsoft introduced bug.

This is the third or fourth bug we have seen in as many months with Access, I'm not impressed.

Edit - after some tests Access version 2204 does not suffer this.

Access version 2205 does :
Was this answer helpful?

6 people found this answer helpful.

0 comments No comments
Anonymous

2022-05-25T13:34:13+00:00

There seems to be multiple causes to this issue: ID int fields, nvarchar in the PK and/or datetime fields.

When I was researching tried everything and the only update that worked for me was changing the nvarchar fields in the key to varchar. Having nvarchar in other fields on the table did not matter. That issues wasn't reported in the MS doc - as far as I could find - but it was the common element to our tables having the issue (only 4 of 10) in O365 Access linked to a SQL Server 2016 db (in 130 compat mode).
Was this answer helpful?

3 people found this answer helpful.

0 comments No comments
Anonymous

2022-05-25T13:11:42+00:00

The issue is to create an ID in MS SQL which MS Access does not understand.

E.g. I created an ID as bigint and got this issue...after changing it to int it worked...
Was this answer helpful?

0 comments No comments