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.

Thanks, Minty. As I replied earlier, switching primary keys to smaller datatypes (Nvarchar -> varchar, BigInt -> Int) seemed to have the desired effect, but I also tried updated to ODBC Driver 18 for SQL Server and that had the same effect with less effort and potential impact. This will also be viable for those getting data from commercial DBs where they cannot change datatypes but should be able to configure their client machines. Thank you and Lisa both for your inputs.

@Ryan - Very glad to hear the driver update worked for you and hopefully that will be enough to resolve the issue for others. I will add that before my key change I had tried every suggestion I found posted... updating to the 18 driver turning on Access compatibility settings removing milliseconds from datetime fields downsizing to smalldatetime downgrading the compat level on the database (multiple versions tried) None of that worked for me. The app/database is something I inherited that had not been configured with best practices. Some of the key fields were holding values that would never be a length beyond 4 characters yet they were defined as nvarchar(255) :-| Obviously someone took a shortcut and generated a table by importing a spreadsheet or whatever so for me the opportunity to clean up was also there.

Thank you. I had the same issue. By changing the nvarchar fields to just varchar fixed all of our issues.

That is correct but only where the key filed is a nvarchar. All other nvarchar's do not need to be changed. I'm not exactly sure how many versions of Access this affect but still works fine on 2016 ver 2204 but fails on 2205. Anyone have good links to revert back to the previous version.

I had exactly the same problem. Glad I found the best workaround here so far ---- change the PK from nvarchar to varchar. At least it works for now. Thanks again everyone.

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-26T20:23:47+00:00

Thanks, Minty.

As I replied earlier, switching primary keys to smaller datatypes (Nvarchar -> varchar, BigInt -> Int) seemed to have the desired effect, but I also tried updated to ODBC Driver 18 for SQL Server and that had the same effect with less effort and potential impact. This will also be viable for those getting data from commercial DBs where they cannot change datatypes but should be able to configure their client machines. Thank you and Lisa both for your inputs.
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-26T20:47:20+00:00

@Ryan - Very glad to hear the driver update worked for you and hopefully that will be enough to resolve the issue for others.

I will add that before my key change I had tried every suggestion I found posted...

updating to the 18 driver

turning on Access compatibility settings

removing milliseconds from datetime fields

downsizing to smalldatetime

downgrading the compat level on the database (multiple versions tried)

None of that worked for me.

The app/database is something I inherited that had not been configured with best practices.

Some of the key fields were holding values that would never be a length beyond 4 characters yet they were defined as nvarchar(255) :-|

Obviously someone took a shortcut and generated a table by importing a spreadsheet or whatever so for me the opportunity to clean up was also there.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2022-05-29T01:54:39+00:00

Thank you. I had the same issue. By changing the nvarchar fields to just varchar fixed all of our issues.
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-29T02:28:51+00:00

That is correct but only where the key filed is a nvarchar. All other nvarchar's do not need to be changed. I'm not exactly sure how many versions of Access this affect but still works fine on 2016 ver 2204 but fails on 2205. Anyone have good links to revert back to the previous version.
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-29T07:49:59+00:00

I had exactly the same problem. Glad I found the best workaround here so far ---- change the PK from nvarchar to varchar. At least it works for now. Thanks again everyone.
Was this answer helpful?

0 comments No comments