asked on

MS Access with ODBC: how have alternate key with primary key?

I have created an Oracle table with a primary key and two alternate keys. When I link to it via odbc using Oracle in OraHome92 driver, I loose the PK and one of the AKs. Access or ODBC chooses one of the AKs as the PK and the original PK only has a non-unique index.
I ended changing the AKs to Inversion Entries, keeping the indexes, but loosing the uniqueness that I need. Any help would be much appreciated!

ASKER CERTIFIED SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Greg_Beam

ASKER

Ok. I need ODBC to recognize the PK. What is happening now is that in order to get ODBC to recognize the PK, I need to change the AKs ti IEs, if I don't, odbc won't recognize the PK.

I don't know how to override odbc's detection of keys in access.

Greg_Beam

ASKER

By loosing uniquesness, I mean that there is still an index on the key, but it is a non-unique index. I need the PK index to be unique. I think I was getting the "#deleted" problem without it, but now that I think about it, that may have been due to a different problem. I could try leaving the PK as odbc assigns it and see what problem that causes.

Greg_Beam

ASKER

MS Access, or ODBC,needs to have a PK. The problem is that when there are AKs on the table, ODBC gets "confused" and does not know which unique index to chose. The PK is always present and unique while the AKs are unique if present, but can be null, so they do not make a useful PK. The only way to get ODBC, using this driver anyway, to recognize the PK, was to remove the AKs, change them to IEs. This is unfortunate in that the application would benefit from the AKs.

I guess I was hoping that someone would know how to override ODBC's determination of the PK. I have searched for an answer and do not see any opportunity to do this. I am thinking now that this is an inherent limitation of ODBC which is to be accepted.

Greg_Beam

ASKER

The answer was right, but there were other problems I had to correct before I realized this answer is correct.