Access returning the wrong data from ONE Oracle table

Question

I have three Oracle tables, A, B, and C that I hit using MS Access (2007 and 2010) through an ODBC connection.  Each table has an ID field which is used to link the tables.  If from Access I run the following SQL statement (I can type the SQL or use Query Builder, same results):

SELECT * FROM B WHERE ID=500

It returns the the data for the FIRST record (ID=1)....not the record with ID=500

To Make things stranger, if I run the following SQL Statement:

SELECT * FROM A, B, C WHERE A.ID=B.ID AND B.ID=C.ID and A.ID=500

The data returned for table A and C IS for record ID=500, however the data for table B is for the first record in the table (ID=1).

If I run a SQL that returns multiple rows....all rows show table B data from the FIRST record.

Of course the Oracle DBA is telling me it is a problem with Access.  If we use other tools to hit the table they all return the correct results.  So only Access is getting these strange results.  It is not a table size issue because I hit much larger Oracle tables everyday without issue, infact tables A and C are already much larger than table B.....it is just this one table that ALWAYS returns only data from the first record.

I also created a form and coded a VBA program to create a recordset that produced the same results.

Has ANYONE ever seen something like this before?

Thanks, Randy

Answer 1

OK, I have discovered the problem.  I found someone who is having the exact same problem on another forum site.

The Oracle table has a Primary Key Index defined on the MEMBER_ID field with Unique set to Yes.  However, the table has another index defined on a ONLINE_MEMBER_ID field with the Unique set to Yes.  The ONLINE_MEMBER_ID field is Null for 90% of the records in the table.

MS Access sees the Unique set to Yes and is MISTAKING the ONLINE_MEMBER_ID index as the primary key index.

I am now going to reserch if there is ANYWAY to tell MS Access which index to use.

The person in the other forum resolved his problem by changing the query to a pass-through query.

Randy

Answer 2

Yes what you are saying is correct.  Inaddition, if you run one SQL that joins the three tables the data for table A and C is for ID=500, but the data for table B is for ID=1...even though table C was linked "through" table B.

For all three tables the ID field is defined as Numeric(38).

Randy

Answer 3

So what you are saying is that:

SELECT * FROM A WHERE ID=500

SELECT * FROM C WHERE ID=500

both work, but

SELECT * FROM B WHERE ID=500

does not.  Right?!

In table B, what is the data type of your ID field?  As a comparison, what is the data type of your ID field in tables A and C?

Daniel Pineault