?
Solved

How to access Access from Oracle

Posted on 2003-03-11
1
Medium Priority
?
680 Views
Last Modified: 2009-12-20
I have some data in an MS Access datafile.
How do I set up things so that I can run SQL queries against this database from an Oracle database.

I tried to read the "Oracle9i Heterogeneous Connectivity Administrator's Guide" but that somehow lack some hands on examples.

I think I need to:
Create a DSN (ODBC datasource)?
Create a database link?

I run everything in a Win2k environment.

thanks Rene
0
Comment
Question by:renewest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 2

Accepted Solution

by:
Datamonkey earned 150 total points
ID: 8110906
I was about to type a long explanation but then I remembered a article on oracle metalink.
this is note 109730.1 and should answer most of your questions:

PURPOSE
-------
This article provides a step-by-step guide for setting up and troubleshooting
generic connectivity using ODBC, also called Heterogeneous Services,
for a Windows NT platform.
 
SCOPE & APPLICATION
-------------------
This bulletin describes a setup of Oracle 8.1.6 running on Windows
NT 4.0 to access a MS Access 97 MDB file using the ODBC driver:
Microsoft Access Driver.

How to setup a database link between a Microsoft MDB file and the
Oracle Database using Generic Connectivity for ODBC
-----------------------------

1) With the OUI (Oracle Universal Installer) install
   Generic Connectivity using ODBC. This product is part of the server
   installation, because a listener is needed.
   A directory called HS is created.
     
2) The Generic Connectivity utility needs data dictionary tables in the
   Oracle database. To check their existence, run a query on
   i.e. SYS.HS_FDS_CLASS.
   If it fails, run the caths.sql script located in ORACLE_HOME\RDBMS\ADMIN\
   as user sys or internal.

3) Install the third party ODBC driver from Microsoft or Merant.

4) With ODBC Admin Utility configure a SYSTEM DATASOURCE.
   i.e. specify as Data Source Name mstest and select as mdb file
   the Northwind.mdb from Microsoft.
   
5) Now configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
   Add the following lines to the file:

   hsodbc.de.oracle.com  =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)
        (HOST=<HOSTNAME>)           |<- adjust hostname
        (PORT=1521))                |<- adjust port
        (CONNECT_DATA=(SID=hsodbc))
        (HS=)
      )    


  Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

6) Configuring listener.ora:
   This file is in ORACLE_HOME\NETWORK\ADMIN.
   Add the following line to the SID_List of the listener.ora and restart the
   listener afterwards. ( After the restart a service handler for hsodbc should
   exist).

        (SID_DESC=
           (SID_NAME=hsodbc)
           (ORACLE_HOME=e:\ora816)  |<- adjust Oracle_Home directory
           (PROGRAM=hsodbc)
         )
   
   Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

7) Adjust the configuration file of the gateway.  It is located in
   ORCALE_HOME\HS\ADMIN. The name depends on the SID you use for the
   Heterogeneous Service.
   In our sample the listener SID is hsodbc and so the configuration file
   is inithsodbc.ora.

   # This is a sample agent init file that contains the HS parameters that are
   # needed for an ODBC Agent.
   #
   # HS init parameters
   #
   HS_FDS_CONNECT_INFO = mstest    |<- ODBC Data Source Name configured in step 4
   HS_FDS_TRACE_LEVEL = 0          |<- trace levels are from 0 to 4 (4 is maximum)
   


8) Testing the connectivity between Oracle database and the mdb file:
   - create a datbase link:
     create database link hsodbc using 'hsodbc';
   - select * from article@hsodbc;


###################################################################################

COMMON ERRORS and SOLUTIONS

###################################################################################

/********************************************************************/
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from HS
/********************************************************************/
cause:
    This indicates a problem with the Oracle configuration files.
Action:
    Make sure the HOST parameter in the tnsnames.ora file is correct.
    Make sure the PORT number is correct.
    Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA
 
/********************************************************************/
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for ODBC][H001] The environment variable
<HS_FDS_CONNECT_INFO> is not set.
ORA-02063: preceding 2 lines from HS
/********************************************************************/
cause:
    Incorrect parameter settings in the HS init.ora file.
Action:
    Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name.
    Example: HS_FDS_CONNECT_INFO = mstest
    Make sure the HS init.ora file exists in the ORACLE_HOME\hs\admin
    directory and has the same name as the SID in the LISTENER.ORA.
    Example: If SID=hsodbc in the listener.ora file, then the HS init.ora file
    would be named ORACLE_HOME\hs\admin\inithsodbc.ora
 
/********************************************************************/
ORA-00942: table or view does not exist
[Transparent gateway for ODBC]DRV_OpenTable: [MERANT][ODBC SQL Server
Driver][SQL Server]Invalid object name '%table%'. (SQL State: S0002; SQL Code:
208)
ORA-02063: preceding 2 lines from HS
/********************************************************************/
cause:    
    The data source in the odbc.ini file has incorrect database information.
Action:
    Consult your odbc user guide on how to set the parameters for your
    datasource.
 
/*********************************************************************************/
ORA-00942: table or view does not exist
[Transparent gateway for ODBC]DRV_OpenTable: [MERANT][ODBC Paradox
driver][Paradox]Network initialization failed. (SQL State: S1000; SQL Code:
11265)
ORA-02063: preceding 2 lines from PARADOXLINK
/*********************************************************************************/
CAUSE:
      This indicates a network problem.
Action:
      Paradox databases consist of datafiles and lock files.  The lock files are used
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question