CODEWITHSUNDEEP
oracle-databasems-access
realsnad
realsnad

Reputation: 45

#Deleted for values of a linked Oracle table in MS Access

I've created a simple table in Oracle 12c like so:

CREATE TABLE TEST
(
ID NUMBER GENERATED ALWAYS AS IDENTITY,
TEXT VARCHAR2(2000 CHAR),
CONSTRAINT ID_PK PRIMARY KEY (ID)
);

Then I linked it in MS Access using ODBC driver. The problem is that when I input value into TEXT and click away both ID and TEXT show #Deleted. My value gets recorded in the database but I have to requery in MS Access in order to see it.

I also noticed that if I change the datatype of TEXT field to NUMBER, it works fine. After saving the record in MS Access both auto generated ID and value in TEXT field are there. I don't have to requery anything.

This happens only when inserting. Updating works just fine.

Please advise.

Upvotes: 0

Views: 760

Answers (2)

Alexander Remesch
Alexander Remesch

Reputation: 623

In my case, the Oracle ODBC driver (using the rather old version 11.02.00.01 that otherwise works ok and Microsoft Access 2016 32Bit) seems to use the unique indices and not the primary key constraint for determining the primary key.

I had a field with NUMBER(11) as PK with an unique index, then added a VARCHAR2 field with another unique index. The name of the index of the VARCHAR2 field was alphabetically before that of the NUMBER field.

Now, the linked table in Microsoft Access showed the VARCHAR2 field as primary key and I had the problem with '#Deleted' appearing after entering & saving a record as you describe.

After renaming the unique index on the NUMBER field in Oracle to be alphabetically before that of the VARCHAR2 field and re-linking the table in Microsoft Acces, the NUMBER field was the primary key again in Microsoft Access and the '#Deleted' problem was solved.

Upvotes: 1

ccarpenter32
ccarpenter32

Reputation: 1077

So, it would appear you already found the solution, but this is more of an explanation as to why it works that way. Simply speaking, if the base-table uses non-integer values as primary keys, Access rounds these integers to the nearest whole number and then (since it was not a numeric value) Access can no longer find the applicable records. So, changing the data type from TEXT to INTEGER in the table structure would give you your desired result.

Alternatively, if you're using a query to run through these, if you cannot change the keys in the Oracle table then altering the Access query type to a snapshot (in the query properties) will also bypass this problem. But from the sounds of it, this is not how you are utilizing the data.

Upvotes: 1

Related Questions