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

Greg_Beam
Greg_Beam used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Oracle will use and enforce all the keys correctly whether ODBC recognises them or not. Please explain what you mean by "loosing the uniqueness".

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial