MS-Access Connectivity With Oracle

 MS-Access Connectivity With Oracle

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.