CODEWITHSUNDEEP
sqlms-accessoracle10godbcms-access-2007
dave
dave

Reputation: 1364

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

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"

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.

Upvotes: 0

Views: 2071

Answers (3)

user417408
user417408

Reputation: 21

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.

Upvotes: 1

dave
dave

Reputation: 1364

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

Upvotes: 0

Brian
Brian

Reputation: 13571

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'

Upvotes: 1

Related Questions