Re: ORACLE ODBC Driver With Microsoft Access - HELP!!

Hello from across the pond:

Welcome to the limitations of ODBC (it's a small price to pay for connectivity).

The short of it: sorry, you just can't do that. Take a look at the SQL that Access churns out for outer joins. Compare it to Oracle's. Pretty darn different, and it's not translated by the ODBC driver.

Better solution: for queries you use frequently, create Oracle views and attach to them. You say you're using an Access function on some of the fields. I take that to mean that Oracle does not provide the functions you need to get the info your want. In that case, you can still create and attach to the Oracle view. Then bring that view into an Access query, where you can perform your Access functions on the appropriate fields from your view.

If your join is on fields that are the RESULT of an Access function, you may need to rethink your table structure!

By the way, doing the joins on the server side takes advantage of the client-server model's benefit (i.e., the server's power). Performing joins in the client app is inefficient. _Defining_ joins on the client app is okay (for example, in pass-through queries), provided your server can understand the syntax.

Best of luck!

-John Hopkins

Hicom Technology wrote in message <01bcf057$2bc3bb40$05000096_at_kirk>...
>Using Microsoft Access version 2.0 with Oracle 7.2 and Oracle's 16bit ODBC
>driver (not level 2)...
>
>Attaching Oracle tables to Access and setting up a query between two
>related tables does not permit left or right outer joins, an ODBC error is
>reported stating something like "SQL is incorrectly formatted".
>
>I must be able to do left/right joins and cannot use a passthrough (as the
>query calls an Access function) - help me please!
>
>john.sandserson_at_hicom.co.uk
Received on Thu Nov 13 1997 - 00:00:00 CST