Access query to Oracle tables via ODBC failing

Hi there. I have a problem that I hope someone can help me with.

We are migrating the back end of an Access 97 database to Oracle. I have the new Oracle tables created and populated, and linked in the Access database. If I open up the tables, I can see the data just fine, so it appears that the ODBC connection is working OK.

Unfortunately, there are many queries that won't work, and I have been working for several days trying to figure out how to fix them, to no avail.

It seems that whenever a query has both an outer join and an order by clause, things go bad. I get one of the 2 following error messages:

ODBC - call failed (I click <OK>)
[Oracle][ODBC Oracle Driver] [Oracle OCI] ORA-00936: missing expression (#936)

or

<same as above except for> ORA-00933: SQL command not properly ended (#933)

The sql expressions do have the proper semicolons at the end. If I change the query so it has only one or the other of outer joins and order by clauses, but not both, it works. Of course, that doesn't give me the data I want.

I also can't get any SQL Passthrough query to work. When I try, I get:

ODBC - call failed (I click <OK>)
[Oracle][ODBC Oracle Driver] [Oracle OCI] ORA-00911: invalid character (#911)

This happens with even the simplest query, such as:

SELECT * FROM BPAC; (BPAC is the name of a table.)

Has anyone seen these problems before? I'm using the Oracle73 ODBC driver. From what I've read, I believe that any query from access is supposed to work through ODBC on Oracle tables, although some should be rethought for the sake of network effeciency.

I have tried the obvious soultion of having one query that pulls the data with the outer join, and then another that queries the first and sorts, but I still get the same error. I guess Jet is passing it to the server as one operation.

Any help would be extremely appreciated. I'm fairly familiar with Access but know little about Oracle, and I've tried everything I can think of. All I can say is,

help?

I will be watching both newsgroups I'm posting this to, but my news server doesn't get all of the messages, so I would appreciate it if any answers are emailed to me as well as posted.

Thanks,

Ben Beishline Received on Thu Mar 19 1998 - 00:00:00 CST