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-06-01T10:33:45+00:00

    Check your PK definitions on those tables. If they are using nvarchar, change them to varchar. That change got rid of the issue for me.

    @Lisa Greenwood, OMG thank you so much :-D Have a DB I wrote nearly 20 years ago (started with Access 2.0 :-O ) that is still business critical and one simple lookup table suddenly went #Deleted :-( quick change to varchar and it's back up and running :-D

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. George Hepworth 23,120 Reputation points Volunteer Moderator
    2022-05-30T13:26:40+00:00

    Which ODBC driver do you now have installed? ODBC 18? If so, I'm surprised to learn that it doesn't fix the problem with PK's.

    To "roll back" the driver, though, you simply install the other version, ODBC 17, the same way you installed ODBC 18 on all of the computers which use your accdb. Then relink the tables in the SQL Server database to your Access accdb using that driver. Finally, distribute this version of the accdb to all users.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. 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
  4. 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
  5. Anonymous
    2022-04-27T17:56:25+00:00

    Unfortunately, it appears that the issue has returned in version 2202 (Build 14931.20274).

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments