Heterogeneous Service (HS)Connectivity error

[jennifer2007]

Hi Friends,

I follow this procedure correctlty...but I still can not successfully
connect to my access db. Please help.

=========================== Procedure to configure HS ==============

Connecting Oracle with MS-Access
Submitted by admin on Sun, 2005-05-15 06:00. RDBMS Server
This article shows how Oracle's Heterogeneous Services can be configured to allow a database to connect to a Microsoft Access database using standard databases links. The method described can be used to connect to MS-Access from about any platform - Unix/ Linux or Windows.

MS-Access 2003 and Oracle 10g Release 1 are used to illustrate the concepts. However, this procedure should work with Oracle 8i, 9i and 10g databases, as well as various versions of MS-Access.

Step 1: Prepare the MS-Access environment

If you do not have a MS-Access environment, start by installing the required software and create a test table.

Step 2: Define ODBC connectivity

Use the ODBC Administrator Utility to define a local System DSN that can be used to connect to the Access database (same machine). Ensure that the correct *.MDB database file is selected.

Step 3: Prepare the Oracle Environment

Install the Oracle Database Server software on the same machine where MS-Access is installed.

NOTE: It is not sufficient to only install Client Software, as we require an Oracle Net Listener and the Heterogeneous Services (ORACLE_HOME\hs directory) software to be installed as well.

Step 4: Configure and Start the Oracle Listener

Configure the Oracle Listener on the Windows machine. Here is a sample LISTENER.ORA entry that can be used. Change the HOST, PORT and ORACLE_HOME entries to match your setup. You may also use a different SID_NAME if required.


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_windows_machine_name)(PORT = 1521))
)
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = c:\Oracle\Ora101)
(PROGRAM = hsodbc)
)
)
Stop and start the listener service or from the command line:
C:\> lsnrctl stop
C:\> lsnrctl start

Step 5: Configure Oracle HS:

Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name (ODBC1 in our case as defined in step 3).


HS_FDS_CONNECT_INFO = odbc1
HS_FDS_TRACE_LEVEL = off
Note: If you used a custom SID_NAME in step 4, name the file accordingly - INIT.ORA.

Step 6: Configure Oracle connectivity to Windows Machine

From now on we are going to work on the Oracle Server (Unix or whatever you run) add the following TNSNAMES.ORA entry:


access_db.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.31) (PORT = 1521)
)
(CONNECT_DATA =
(SID = hsodbc)
)
(HS=OK)
)
Ensure you can tnsping the new entry before continuing.

Step 7: Create a database link

Create a database link using the entry defined in step 6.


SQL> CREATE DATABASE LINK access_db USING 'access_db.world';

Database link created.
The tables in the access database can now be queried from the Oracle environment.


SQL> SELECT * FROM my_access_tab@access_db;

ID Field1 Field2
---------- -------------------- --------------------
1 row1col1 row1col2
2 row2col1 row2col2
3 row3col1 row3col2

SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM my_access_tab@access_db;

Table created.

========================== end of procedure ==================

But when I create my database link, I got this error.

SQL> CONNECT SYSTEM/oracle
Connected.
SQL> CREATE DATABASE LINK access_db USING 'access_db.world';

Database link created.


SQL> select * from emp@access_db;
select * from emp@access_db
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified (SQL State: IM002; SQL Code: 0)
ORA-02063: preceding 2 lines from ACCESS_DB

I have configured my ODBC for Ms Access driver also.
Where could I have gone wrong?

========== My listener.ora=======




=========== My tnsnames.ora

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

access_db.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hsodbc)
)
(HS=OK)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

============== My listener.ora

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = orcl)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.56)(PORT = 1521))
)
)