Connecting Oracle with MS-Access

MS-Access   and Oracle 10g Release  are used to illustrate the concepts. However, this procedure should work with Oracle 8i, 9i ,10g and upper 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 PubEvent table.

Capture

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.

Use the Microsoft ODBC Administrator Utility to define local connectivity

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 Pubevent@access_db;

  ROWAUTOID EVENTTYPE    EVENTDATE      EVENTIME  EVENTNAME      EVENTCARD
---------- -------------------- ---------------------------------------------------
         1  1            2014/01/19     15:51:07  Invalid Card   0011420921
         2  1            2014/01/19     15:51:07  Invalid Card   0011420922
         3  1            2014/01/19     15:51:07  Invalid Card   0011420923

SQL> CREATE TABLE Pubevent AS SELECT * FROM Pubevent@access_db;
 Table created.
Thanks to all

 

Advertisements