MS Access Displays #Deleted in Every Field for Certain Tables

Anonymous
2022-01-31T20:26:19+00:00

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.

Microsoft 365 and Office | Access | For business | Windows

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

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-05-26T19:39:35+00:00

    Lisa,

    Thank you very much for the info. I am using SQL Server 2019 and changing the primary keys from NVARCHAR to VARCHAR seems to work. I'll need to examine the implications of this, but I think it will work for us.

    For those experiencing this issue with O365, I was only seeing this on machines with (Version 2205 Build 16.0.15225.20028)-32-bit. I have not heard any complaints from 2019 users or 64-bit users. Version 2204 seems to still be functioning as well. If you are in a place that you could roll back to this version until this is fixed, it would be much easier than reworking a DB full of keys (if that is even an option for you).

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. 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
  3. Anonymous
    2022-03-08T20:04:24+00:00

    We also started having the same problem with some 43 tables. We use Office 365. I was able to resolve the problem to some degree by adding a second Microsoft to Oracle ODBC that connects after the Oracle ODBC. Its seems to work. All our systems are broken and I had to work many evenings and weekends on this. I think it may have been a MS patch that came thru. It was like a virus spreading from one user to the next. I am still trying to fix each system as table names need to be corrected in the code as well as the linking.

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2022-02-01T21:09:19+00:00

    Thank you GroverParkGeorge. I have tried relinking. I have even tried starting from a blank MS Access database to see if that would help and unfortunately I'm still getting the #Deleted in every field.

    On our ODBC Driver Configuration, we have also tried unchecking on the Enable Query Timeout box on the Application tab; setting the Fetch Buffer Size to 128,000 on the Oracle tab and setting the Pre-fetch size for LONG column data to 20 and also tried 65,535 on the Workarounds tab. These did not improve our situation either.

    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. The number of rows does not seem to matter. I have tables that are working with 8M rows and I have tables that are not working that have 365 rows.

    All of these are vendor delivered tables.

    Thanks for trying to help!

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  5. Anonymous
    2022-03-09T13:44:26+00:00

    Hey KumarG_108,

    I'm not entirely sure about your use case, but I'm just confirming here on my end, but I think the latest update to be pushed to the current channel (Version 2202, Build 14931.20132) may have resolved this issue. I've changed nothing on my test machine except for updating to the latest version, and I'm able to see the values in these tables again. I'm going to run a few more tests here and confirm, but I believe this problem may be resolved now.

    Thanks!

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments