ODBC Link choosing wrong Primary Key

[Basia]

Greetings. I am trying to link an Oracle table through ODBC. It links fine, but chooses the wrong primary key. I have tried to change it through the properties, but it won't let me.

Does anyone have any ideas?

Thanks,
Basia

 

[RiverGuy]

When you originally link the table, it asks you to choose the primary key. Did you not choose it from the list box that contains the fields?

 

[Basia]

I do not get that option box. Is there a way for me to make the ODBC driver give me that option?

Thanks,
Basia

 

[RiverGuy]

What version of Access? I have never seen it not give the option in Access XP. I dont remember in prior versions.

 

[Basia]

Access 2000 - this is making me crazy!

Thanks for any input...Basia

 

[nbateman]

I know it's late, but for the record ....

The reason for your problem is that you have multiple unique indexes/constraints on the table. Presumably one is the oracle primary key, and why that one isn't automatically picked I have no idea.

The only way I have found to get it to pick the right one is to temporarily drop the indexes you don't want to use as the primary key, link the tables in Access, then recreate the other indexes. Problematic on a production db I know.

The reason RiverGuy always gets the option to choose the key is probably because he doesn't have any unique indexes defined on his table (tut tut ;-) ). He might also be linking everything through Oracle views.

Better late than never,

Nick.