Re: Access query to Oracle tables via ODBC failing

What version of SQL net are you using? I have had this problem before when using the incorrect version. I don't know off the top of my head which version of SQL net we use at work but I will check.

Ben Beishline wrote in message <6epp2l$r27$1_at_bdmserver.abq.bdm.com>...
>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 Wed Mar 18 1998 - 00:00:00 CST