Re: Questions regarding MS ACCESS, ODBC and ORACLE

From: Brian M. Biggs <bbiggs_at_cincom.com>
Date: 1996/11/25
Message-ID: <3299D880.1854_at_cincom.com>#1/1

Arnold Hitch wrote:
>
> Has anyone have any comments about problems using MS ACCESS to Oracle
> through ODBC? I have heard there are a number of problems with how ACCESS
> deals with Oracle objects, specifically private and public synonyms. I am
> also curious if any one has successfully had ACCESS perform remote update,
> insert or delete through and Oracle database link.

[Quoted] We have a large Access application that has been ported to Oracle, and we have had very few problems. Most of the application works, except for several queries involving left joins.

We are currently having problems with left joins being performed through the Jet Engine and ODBC to an Oracle database. I can very easily reproduce the problem in Access 2.0. A left join with any other type of join will return either an Oracle 960 - "ambiguous column naming in select list" or 933 - "SQL command not properly ended" error. The exact same query works fine when run against an Access or MS SQL Server database.

This is a sample query as shown in Access (sorry, it's long and ugly):

SELECT DISTINCTROW actv_queue.src_document_typ,

actv_queue.src_document_id, actv_rout.actv_cd, actv.actv_desc,
actv_rout.actv_rout_desc, actv_rout.actv_stat_cd, actv_rout.rqst_id,
actv_rout.resp_id, resp.resp_desc, actv_rout.actv_plan_dt,
actv_rout.actv_actual_dt, actv_rout.actv_priority_nbr,
actv_rout.active_ind, actv_rout.actv_plan_ramt,
actv_rout.actv_actual_ramt, actv_rout.prj_elem_id,
actv_rout.resp_notified_ind, actv_rout.actv_rout_note, actv_rout.upd_ts

FROM actv_queue INNER JOIN ((actv_rout LEFT JOIN resp ON actv_rout.resp_id = resp.resp_id) LEFT JOIN actv ON actv_rout.actv_cd =

actv.actv_cd) ON (actv_queue.src_document_id =
actv_rout.src_document_id) AND (actv_queue.src_document_typ =
actv_rout.src_document_typ);

Has anyone else run into this problem? Is it a documented bug? We are using Oracle 7.1.3 and 7.3.2, and have tried the 1.11, 1.15 and 1.16 Oracle ODBC drivers from Oracle Corporation.

Any help or hints are appreciated.

Thanks,
Brian

-- 
Brian M. Biggs				mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.			voice: (513) 677-7661
http://www.cincom.com/

Received on Mon Nov 25 1996 - 00:00:00 CET