[AccessD] A2K & Oracle10g via ODBC - wrong Primary Key

Paolo Cantoni pcantoni at erggroup.com
Wed Aug 3 20:58:07 CDT 2005


Hi,

I've found a _gotcha_ in using Access 2000 to connect to an Oracle 10g 
database.  I haven't tested this beyond this combination.

It turns out, that if you have more than one unique key defined for an 
Oracle table (at the time you link with ODBC), Access will get confused 
about which is the primary key.  Accordingly, it will mark ALL the rows 
and columns as #Deleted.

It can be worked around by splitting the table definition and loading 
process in two... First declare the table with only a primary key, link to 
it with Access, then add the rest of the table definitions (other unique 
constraints, foreign keys etc...)

Seems to work "without loss of generality" UNTIL you re-link! 

Anyone else observed this or got a fix (as opposed to a workaround).

Thanx,

            Paolo Cantoni, Data Architect
ERG Group
247 Balcatta Road Balcatta WA 6021 Australia
Tel: + 61 8 9273 1287 Fax: +61 8 9273 1535
Email: pcantoni at erggroup.com Website: www.erggroup.com
There is no such thing as an inconsistently correct system...
...Therefore, aim for consistency; in the expectation of achieving 
correctness!


More information about the AccessD mailing list