This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
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.
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.
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!
"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.
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 don't have any other thoughts, then, unless it has to do with the ODBC driver for Oracle.