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-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
  2. Anonymous
    2022-05-19T20:11:51+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.

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2022-04-13T07:12:39+00:00

    A different angle on this problem.

    I was connecting to an Intersystems database over ODBC using Linked Tables and when our database people upgraded the database version, and pushed us onto a new version of the ODBC driver, I ended up getting #Deleted appearing for every cell of every field of my linked tables.

    It turned out to be caused by the new database switching to using BigInt (Large Number) data type and my database was not able to deal with this.

    Luckily my MS Access was new enough that I did not need to update the program (apparently anything newer than Access 2016 build 16.0.7812 should be fine). But I did need to go into the Current Database options and enable the "Support Large Number (BigInt) Data Type" option (while I was there, I also enabled the Support Date Time Extended (DateTime2) Data Type"). This irreversibly updates the ACCDB file to Access 2016, so it cannot then be opened in older versions of Access anymore. (https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=BNETODBC\_INTRO)

    Lastly, i had to go into Linked Table Manager ... select my linked tables and click Refresh in order for Access to apply the BigInt data type into my actual tables. (https://support.microsoft.com/en-au/office/using-the-large-number-data-type-5b623f6e-641d-4e97-8bdf-b77bae076f70)

    After doing this, i was able to view my Linked table fine again... and also run queries against it without any problems.

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2022-02-14T13:29:53+00:00

    Hi,

    We seem to be running into this issue as well. We have some vendor supplied Oracle tables that we were connecting to in Access with an Oracle 19c driver, and any version of office after the locking issues were resolved (Version 2201 Build 14827.20158 I believe). I did find an issue in 2020 where Access was not properly identifying AutoNumbers, but I'm not sure if that's the same case here. Here is the link to that forum post.

    What we had ended up doing to avoid the lock file issues, and now this linked table issue, is block our updates and stay on Version 2112 Build 14729.20260, this seems to resolve all issues. Even the latest update from Feb 8 2022 still shows #deleted records in any linked table from our Oracle data sources. The only thing that has been changing on our setups is the version of Access, which also leads me to believe there is an issue with Access and linked tables.

    Thanks!

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  5. Anonymous
    2022-05-31T02:05:20+00:00

    I'm not changing anything. I'm using MySQL. The problem now is Office version 2205 is breaking the ODBC. Linked tables with varchar as primary keys can't be read out of sudden! This is a critical Microsoft problem.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments