Oracle-L: Re: access/oracle

From: Thomas Day <tday6_at_csc.com>
Date: Mon, 03 Feb 2003 07:54:06 -0800
Message-ID: <F001.00542256.20030203075406@fatcity.com>

We send out Access users to a datamart where we have replicated and de-normalized tables for them to play with. We don't allow them to query the production database since they are bound to eventually come up with the ad-hoc sql query from hell that will bring the database to a screaching halt.

Point this out to management and have them get you a datamart server.

                                                                                                                                       
                      becker.bill                                                                                                      
                      @marshfieldclini         To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      c.org                    cc:                                                                                     
                      Sent by: root            Subject: access/oracle                                                                  
                                                                                                                                       
                                                                                                                                       
                      01/31/2003 05:24                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L

Hello,

We are stuck with a group of users who insist on using MS access 2K 9.0 on a Win2K workstation as an adhoc query front end to an Oracle 8.1.7.4 db on Sun.

The problem is that their queries run OK when executed from sqlplus directly against Oracle, but are terrible when executed from access. This is because access has tables linked to the oracle tables, and access insists on downloading all the data, performing the joins and where clause filters on the local workstation.

I don't know much about access, and don't want to; however, this has been dumped on us, so I am hoping that someone has experienced the same problem and found a solution. No, we can't dump access, and use something different; for reasons beyond our control (management), we have to make this work.

I think the solution is to somehow force the query to be processed on the Oracle machine, and return only the result set. We have tried the access "passthrough" option, but I believe that fails due to the newer ansi syntax used by access. (OUTER JOIN, INNER JOIN, etc.) The users insist on using access's gui query generator.

Has anyone found a solution to this? Is there a good whitepaper somewhere like,
"Using Access as a Front-End Query Tool to Oracle?" (I will search google)

Thanks to all who have read this far,
More thanks to any responders,
Most thanks for any helpful advice

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: becker.bill_at_marshfieldclinic.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Day
  INET: tday6_at_csc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Feb 03 2003 - 09:54:06 CST