Re: MS Access selecting from Oracle database
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Mar 2008 14:25:45 -0500 (CDT)
> We have a user who wants to select information into Access from Oracle.
> What has to be setup to do this?
The first thing you'll need is a PO for more hardware. I'm only partially
kidding. In a past life, many Access databases (including user-created and
maintained ones) were fed from the production Oracle DB. Enter Microsoft
patching and upgrades. One fine day, the phone starts ringing off the hook
because "the database is slow". Upon launching a fine graphical performance
tool on the Oracle ERP DB, I instantly saw the problem -- some of the
largest tables in the DB each had multiple FTSs running against them. The
cause? A Microsoft update to MDAC on the client PCs, which included the
ODBC driver. Somehow this caused Access (of Evil) to think it needed to
pull down all rows from all tables in a multi-table query instead of sending
a single SQL statement with a handy WHERE clause. Similar issues arose when
users were upgraded from Windohs 2000 to XP, and when MS Access (of Evil)
was patched and upgraded.
Look very hard at the business need the user has for this request. Access
(of Evil) and other ad-hoc query tools can and do cost businesses money in
terms of server performance, database performance, network performance,
recovery and maintenance of those ancillary applications, downtime, and
possibly worst of all -- trusting that these ad-hoc queries were written
with a full understanding of the data being presented therein. For example,
many third-party systems keep the business views outside the database. This
can cause misinterpretation of critical business data from ad-hoc tools that
do not or cannot adhere to those views.
Other than that, just install an Oracle Client on the PC, configure Oracle
networking (e.g. sqlnet.ora and tnsnames.ora) and create an ODBC link to
your Oracle database that Access (of Evil) can use as a linked table.
But I wouldn't recommend it....
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
