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: Oldest
Most helpful Newest Oldest
  1. 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
  2. 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
  3. 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
  4. 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
  5. 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