Use Oracle Heterogeneous Services To Connect Oracle To MS Access On MS Windows Platform

Heterogeneous Services is an integrated component within the Oracle server, and provides the generic technology for accessing non-Oracle systems from the Oracle server.

This article shows how Oracle's Heterogeneous Services can be configured to allow an Oracle Database to connect to a Microsoft Access database using standard databases links on Microsoft Windows Platform.

Step 1: Prepare the MS-Access environment

If you do not have a MS-Access environment create a new Access file and create a table with the name EMPLOYEE
The table EMPLOYEE will have the following columns:
1- ID Number
2- NAME Text
3- SAL Number
4- JOB Text
Start inserting data to the EMPLYEE table like that













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 on the 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.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(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

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 = 192.168.1.200) (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';



Step 8: Test Configuration

The tables in the access database can now be queried from the Oracle environment.
SQL> SELECT * FROM employee@access_db;
id name sal job
-- ------------ ---------- ------
1 Ahmed 1000 DBA
2 Mohamed 109 IT
3 Mahmoud 100 Java
SQL> INSERT INTO employee@access_db
values(4,'Ali',1000,'Network Admin');

SQL>COMMIT;