Wednesday, July 1, 2015

Connecting Oracle with MS-Access



Dear my readers,

Here i explain how to link with from Oracle to Microsoft ACCESS and import table from 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.
Step 1: Prepare the MS-Access environment
For example db1.mdb is our ms access file and my_access_tab is our 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 = yaser)(PORT = 1521))
      )
    )
  )SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = odbc1)
   (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
   (PROGRAM = hsodbc)
  )
(SID_DESC =
(GLOBAL_DBNAME = Oracle8)
(SID_NAME = ORCL)
)
)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).
And rename this file inithsodbc.ora  to be initodbc1.ora
HS_FDS_CONNECT_INFO = odbc1
HS_FDS_TRACE_LEVEL = off
Note: name the file accordingly - INIT.ORA.   + SID_NAME in step 4,
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:
odbc1 =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = yaser) (PORT = 1521)
  )
  (CONNECT_DATA =
      (SID = odbc1)
  )
  (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 myaccess USING "odbc1"; 

Database link created.
The tables in the access database can now be queried from the Oracle environment.
SQL> SELECT * FROM my_access_tab@myaccess;

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

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

Table created.

Best regards with Yasser

No comments:

Post a Comment