| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: access/oracle
Bill
The way we have avoided the problem you describe is to create daily a special set of tables that contain the data needed in Access. These tables are then pulled over to Access and then reports are created in Access. If the Oracle tables are part of an OLTP, the information probably needs some processing before it is useful in Access. That is probably what they are doing by joining the tables. Another idea might be to see if there are any connection properties in Access that you could change.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
[mailto:becker.bill_at_marshfieldclinic.org]
Sent: Friday, January 31, 2003 4:25 PM
To: Multiple recipients of list 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).
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 Fri Jan 31 2003 - 16:59:31 CST
 |
 |