Re: MS Access selecting from Oracle database

> 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

--
http://www.freelists.org/webpage/oracle-l