Connecting Oracle with MS-Access
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
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.
1. Oracle Heterogeneous Connectivity Administrator's Guide.
2. Oracle Metalink Note 109730.1 - How to setup generic connectivity (Heterogeneous Services) for Windows NT.