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.

"MS released the fix to the Insiders Channel yesterday and it should be available for the current channel probably later today" https://www.access-programmers.co.uk/forums/threads/new-access-sql-backend-bug-deleted-showing-in-tables.323388/post-1829306 Anyone confirm or see this fix yet?

I don't have any other thoughts, then, unless it has to do with the ODBC driver for Oracle.

Only one of the tables prompts me to define the primary keys, the rest are automatically getting that defined as they are linked. I've checked through the tables in Design View and I can see the appropriate primary key is being defined by Access, so I don't think it is losing that information as we're pulling it into Access.

"I have explored additional tables and there does seem to be a trend that if the primary key(s) are number then MS Access will handle it fine and we can see the contents of that table. If even one of the primary key(s) are char or varchar2, then we see the #Deleted value in every field." That suggests quite clearly that Access is not recognizing these fields as a Primary Key or part of a composite Primary Key. And that, in turn, suggests the place to dig deeper. You have an Oracle database, which I haven't used for a long time and don't recall specifically. So this may or may not be useful. When you initially link to the tables in the Oracle database, do you see a dialog like this? It appears when Access can't determine the Primary Key. In this case, CityID WOULD be the PK, but it's not designated as such. Perhaps you need to select the field or combination fields that make up the PK in these tables. The set up for Oracle may be different, but look for a similar feature .

One possible problem is that the tables need to be relinked with the current ODBC driver AND you must make sure when you do that to indicate to Access which field(s) are the Primary Key field(s) for each table.

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-06-02T12:43:08+00:00

"MS released the fix to the Insiders Channel yesterday and it should be available for the current channel probably later today"

https://www.access-programmers.co.uk/forums/threads/new-access-sql-backend-bug-deleted-showing-in-tables.323388/post-1829306

Anyone confirm or see this fix yet?
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
George Hepworth 23,120 Reputation points Volunteer Moderator

2022-02-02T00:51:45+00:00

I don't have any other thoughts, then, unless it has to do with the ODBC driver for Oracle.
Was this answer helpful?

0 comments No comments
Anonymous

2022-02-02T00:17:40+00:00

Only one of the tables prompts me to define the primary keys, the rest are automatically getting that defined as they are linked. I've checked through the tables in Design View and I can see the appropriate primary key is being defined by Access, so I don't think it is losing that information as we're pulling it into Access.
Was this answer helpful?

0 comments No comments
George Hepworth 23,120 Reputation points Volunteer Moderator

2022-02-01T22:16:38+00:00

"I have explored additional tables and there does seem to be a trend that if the primary key(s) are number then MS Access will handle it fine and we can see the contents of that table. If even one of the primary key(s) are char or varchar2, then we see the #Deleted value in every field."

That suggests quite clearly that Access is not recognizing these fields as a Primary Key or part of a composite Primary Key. And that, in turn, suggests the place to dig deeper.

You have an Oracle database, which I haven't used for a long time and don't recall specifically. So this may or may not be useful. When you initially link to the tables in the Oracle database, do you see a dialog like this? It appears when Access can't determine the Primary Key. In this case, CityID WOULD be the PK, but it's not designated as such. Perhaps you need to select the field or combination fields that make up the PK in these tables.

The set up for Oracle may be different, but look for a similar feature.
Was this answer helpful?

0 comments No comments
George Hepworth 23,120 Reputation points Volunteer Moderator

2022-02-01T18:57:12+00:00

One possible problem is that the tables need to be relinked with the current ODBC driver AND you must make sure when you do that to indicate to Access which field(s) are the Primary Key field(s) for each table.
Was this answer helpful?

0 comments No comments