Datatype error when linking Oracle tables to Access

[richardmooney]

I'm currently trying to link a table from an Oracle database (Oracle 10g) to an Access database (Access 2000) via ODBC. The link sets up fine, but when I go to view the data in the resulting Access table, every field in the table shows the text '#Delete'.

I've managed to attribute the problem down to the primary key through the process of elimination, and more specifically the error only occurs if the primary key is of an Oracle CHAR datatype.

Could anyone please recommend a way of resolving this problem?

Many Thanks,
Rich

 

[oradba101]

Hi, Rich

Is it possible to change the datatype from "CHAR" to "VARCHAR2"?

Regards,


William Chadbourne
Oracle DBA

 

[PCLewis]

Are you sure that it only happens when of CHAR type?

I've seen the symptoms you have seen but it occurs when you select the wrong combination of fields as the primary key when linking it to your Access database.

If you don't have a primary key defined in your Oracle table that Access likes the look of then when you link the table Access prompts you for a primary key. If you select badly such that all keys are not unique then you get the exact behaviour you see. If you press escape here instead of selecting fields, you will see all the data but will not be able to make changes to the table from Access.

Note: The 10g migration wizard CAN turn a perfectly fine primary key from an Access table into one that Access will not like.

The solution is to change the table in Oracle to have primary keys that make Access happy. from memory it was the UNIQUENESS flag that gets turned off in some cases via the migration wizard.