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.

What I found helped - before fixing the nvarchar fields - was copying the data into a local table. That allowed me to work with it until the linked SQL tables were fixed. Maybe an interim step you could use as well?

The update was forced by Office 365 (similar to the recent (Outlook update) This setting is controlled by the companies Global IT group. I will check with them but doubt if they would have preview updates enabled for all users.

Not possible for me. The Tables are from a commercial application. I am using Access to extract and analyze data and generate reports.

Check to make sure you aren't using "preview" updates. That's what caused our issues. https://docs.microsoft.com/en-us/deployoffice/overview-update-channels https://docs.microsoft.com/en-us/deployoffice/change-update-channels Edit: Looks like "Current" channel still is allowing 2205 version which is broken for MariaDB ODBC connecter.

I believe I found a work-around. When I checked the "Force SQL_WCHAR Support" option on the Workarounds tab and refreshed the links to the Oracle tables, the data was retrieved successfully. To verify, I unchecked the option, refreshed the links again, and the data came back as #Deleted. We are in contact with Microsoft concerning the issue and are awaiting a final resolution. In the meantime, we will utilize this modification to our ODBC DSN. It would be my guess that Oracle Short text fields are stored as wide characters, or at least they have the option to be, I am not a DBA, and when they are, the ODBC layer is not picking up on that with regard to the indexes.

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-24T12:22:29+00:00

What I found helped - before fixing the nvarchar fields - was copying the data into a local table. That allowed me to work with it until the linked SQL tables were fixed.

Maybe an interim step you could use as well?
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-23T23:40:52+00:00

The update was forced by Office 365 (similar to the recent (Outlook update)

This setting is controlled by the companies Global IT group.
I will check with them but doubt if they would have preview updates enabled for all users.
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-23T23:37:47+00:00

Not possible for me.

The Tables are from a commercial application.
I am using Access to extract and analyze data and generate reports.
Was this answer helpful?

0 comments No comments
Anonymous

2022-05-19T13:17:34+00:00

Check to make sure you aren't using "preview" updates. That's what caused our issues.

https://docs.microsoft.com/en-us/deployoffice/overview-update-channels https://docs.microsoft.com/en-us/deployoffice/change-update-channels

Edit: Looks like "Current" channel still is allowing 2205 version which is broken for MariaDB ODBC connecter.
Was this answer helpful?

0 comments No comments
Anonymous

2022-04-28T17:22:49+00:00

I believe I found a work-around. When I checked the "Force SQL_WCHAR Support" option on the Workarounds tab and refreshed the links to the Oracle tables, the data was retrieved successfully. To verify, I unchecked the option, refreshed the links again, and the data came back as #Deleted.

We are in contact with Microsoft concerning the issue and are awaiting a final resolution. In the meantime, we will utilize this modification to our ODBC DSN.

It would be my guess that Oracle Short text fields are stored as wide characters, or at least they have the option to be, I am not a DBA, and when they are, the ODBC layer is not picking up on that with regard to the indexes.
Was this answer helpful?

0 comments No comments