Control which columns become primary keys with Microsoft Access ODBC link to Oracle

Question

When you create a Microsoft Access 2003 link to an Oracle table using Oracle's ODBC driver, you are sometimes asked to state which columns are the primary key(s).

I would like to know how to change that initial assignment, or even how to get Access/ODBC to forget the assignment. In my limited testing I wonder if the assignment isn't cached by the ODBC driver itself.

The columns I initial chose are not correct.

Update: I never did get a full answer on this one, deleting the links then restoring them didn't work. I think it's an obscure bug. I've moved on and haven't had to worry about this oddity since.

Answer 1

It is not possible to delete the link and then relink?

Answer 2

You must delete the link to the table and create a new one. When a table is linked all the connection info about the table's path, structure (including primary key), permissions, passwords and statistics are stored in the Access db. If any of those items change in the linked table, refreshing links won't automatically update it on the Access side because Access continues to use the previously stored info. You must delete or drop the linked table and recreate the link, storing the current connection information.

Don't know for sure if this next bit also applies to odbc linked tables, but I suspect it does. For Jet tables, it's a good idea to periodically delete all links and recreate them to improve query performance, because if a linked table's statistics are made on a table with few records, once that table is filled with many more records, new statistics will tell Jet's optimizer whether using indexes or a full table scan would be the better course of action when running a query.

Answer 3

Are there somone can help for this issue. I have the same issue with the primary key Ms Access/ Oracle. Don't know why when link MS access to the Oracle via ODBC driver, the primary key has been changed.

Answer 4

Hi I have the same issue. I'm would like to change a promary key on linked table but I'm unable as it is a linked table. When I link the tble to MS access, the primary on the linked table go to a different collumn (not the same with primary key on the Oracle). Is it someone know why it happen? THanks