Access 2007 to Oracle 10g linked table -- query with flawed results, but no errors thrown

Question

Access 2007 databases querying linked oracle 10g tables are returning flawed result sets when using the WHERE clause to filter-out unwanted records. Oddly, some filtering is happening, but not reliably.

I can reliably demonstrate/produce the problem like this:

1. Create a *new* database with Access 2007.
2. Create a second *new* database with Access 2007, and then "save-as" 2000.
3. Create a third *new* database with an older version of Access.
4. Run the following query in each database:

SELECT 
    STATUS, 
    ID, 
    LAST_NAME, 
    FIRST_NAME
FROM 
    Oracle10g_table
WHERE 
        STATUS="A" 

• In both databases created with Access 2007, running this query will give you a result set in which some of the records where (STATUS="A") = false have been filtered out, but not all of them.
• In databases created with older versions of access, the where clause filters properly, and the result set is correct.
• STATUS is a text field
• The table is a "linked" table to an Oracle10g Database
• The table has 68k rows
• I've tested my timeout at 60, 1000 and 0

Has anyone run into this problem?

I wonder if this is a new "feature" of access that will also affect 2010. Could this have anything to do with ODBC?

Thanks for any help, - dave

MORE...

I just tried an alternate form of the query, using HAVING instead of WHERE, and it worked! Problem is, besides that this shouldn't change anything (yes -- more virtual tables, but shouldn't change the end result) my end-users will be using the Access 2007 visual query designer, not typing SQL directly, which is going to default any criteria they enter into a WHERE.

Answer 1

My hunch is that one of your ODBC drivers used by Access to connect to Oracle is treating "A" as a column name not the literal 'A'. Have you tried single quotes on the 'A'? In Oracle double quotes are used to reference column names, is there a column named "A" by any chance?

Oracle Query Example #1

Select object_name from all_objects
where "OBJECT_NAME" = 'DUAL'

Oracle Query Example #2

with example as (
Select object_name as "Fancy Column Name" from all_objects
)
select * from example
where "Fancy Column Name" = 'DUAL'

Answer 2

This turned-out to be an ODBC-related issue. Our tech support service unit installs connectivity on each of our workstations -- they program it themselves, and who knows what they actually put into it -- but the net result is that when you link to an ODBC datasource with Access (of any version), our network servers show-up in the 'machine data source' tab, so we just click on the one we want and away we go. This has worked well until Access 2007.

What I did was create a "new" machine data source, which let me choose the ODBC driver myself (instead of making me use the one our tech support folks created). I picked "Microsoft ODBC for Oracle", entered the name of the server I wanted, and that all it took. Now the WHERE-clause inconsistent filtering problem is solved (I hope).

The only thing remaining is to send this back to our tech support folks, so they can clean-up their installation. Should I ask for hazard pay? :-) hehe

Answer 3

I've had a similar problem. In my case, changing the ODBC driver worked, but I could also just change the 'unique record identifier' to a column with no nulls in it. It still doesn't have to be the "right" unique record identifier.