How to query/fetch data of MS Access DB from an Oracle DB?

To day I'll show you how we can read/query data which is in MS Access database from oracle database. it is a step by step description and I assume that you have a oracle database and MS access database. So I'll not discuss of this two database installation or configuration. For this tutorial I've used

• Oracle database 11g version 11.2.0.1.0
• Windows XP SP-3
• MS Access 2007

Steps to complete the tutorial:

• Step1: Creating System DSN (Data Source Name) with Microsoft Access Driver
• Step2: Configuring Oracle Net Listener-listener.ora file
• Step3: Configuring oracle heterogeneous services 11g HS (initaccessdb.ora)
• Step4: Configuring Oracle Local Naming file - tnsnames.ora
• Step5: Creating DB Link in Oracle and accessing data from MS Access database

Step 1: Creating System DSN (Data Source Name) with Microsoft Access Driver

First go to ODBC Data Source Administrator. For this follow the step below:
Go to Start>Programs>Oracle-Oradb11g_home1 (in my case it is home2)>Configuration and Migration Tools> Microsoft ODBC Administrator

In WINDOWS 10 you may not find the Configuration and Migration Tools in start menu. In this case you can open it from control panel> administrative tools>ODBC Data Sources (32-bit)/ODBC Data Sources (64-bit). Remember if your access database is 32 bit then open ODBC Data Sources (32-bit), if it is 64 bit then open ODBC Data Sources (64-bit)

It will open Microsoft ODBC Administrator console

Click on System DSN tab then Click Add button. Select Microsoft Access Driver (*.mdb,*.accdb) then click finish.

Some times you may not find Microsoft Access Driver like above image, specially in widows 7, as bellow image showing blank driver list.

 In this case open odbc driver from following location: C:\Windows\SysWOW64\odbcad32

  

 

It will open the following window. In this window give a data source name. In my case I’ve given the name accessdb. Please remember or note down the data source name. Provide a description, description is not mandatory. Click on select button to select your MS Access database (.mdb or .accdb file).

Then choose your access database file

then click OK> OK> OK it will Save the configuration.

Step 2: Configuring Oracle Net Listener-listener.ora file

Let's locate the file; it is in database home/network/admin directory. In my case: it is
D:\oracle\app\product\11.2.0\dbhome_1\NETWORK\ADMIN  

Note: if you do not find any listener.ora file simply create a file named listener.ora and add the following entry Listener.ora file

   LISTENER =
     (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.201.76)(PORT = 1521)) 
      )
    )
     )

   SID_LIST_LISTENER=
    (SID_LIST =
     (SID_DESC =
      (SID_NAME = accessdb)
      (ORACLE_HOME = D:\oracle\app\product\11.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
     )
   )
  

In first block:
• HOST: the host name is your computer, where database is resides, name or IP address.
In second block:
• SID_NAME: is the name of access DSN Which we create at step 1 in this case it is 'accessdb'
• ORACLE_HOME: oracle database home directory path
• PROGRAM = dg4odbc (leave it as it is)

Step 3: Configuring oracle heterogeneous services 11g HS (initaccessdb.ora)

Go to heterogeneous service directory in oracle database_home/hs/admin in my case it is  
D:\oracle\app\product\11.2.0\dbhome_1\hs\admin

There you will find a file named 'initdg4odbc.ora'. make a copy of this file and rename it as initaccessdb.ora and Add the following text to the file:
HS_FDS_CONNECT_INFO = accessdb
HS_FDS_TRACE_LEVEL = 0

Note: the file name should be init then your access DSN name and extension is .ora.
HS_FDS_CONNECT_INFO: Here we write the name of the DSN created in the ODBC source tool in the first step, so it's 'accessdb'

Step 4: Configuring Oracle Local Naming file - tnsnames.ora

TNS file location is D:\oracle\app\product\11.2.0\dbhome_1\NETWORK\ADMIN in the tnsnames.ora file make an entry like bellow

  accessdb =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.201.76) (PORT = 1521))
  (CONNECT_DATA =
    (SID = accessdb)
  )
  (HS=OK)
  )
  

Here
HOST: Name or IP address of the database server
SID: This is the name of the access DSN name in this case it is accessdb 
 HS=OK: it is a mandatory entry so leave it as it is.

At this point we need to restart oracle database listener service. To do so, Open a command line terminal change your working directory to: Database home/BIN (D:\oracle\app\product\11.2.0\dbhome_1\BIN )

To stop the listener
D: \oracle\app\product\11.2.0\dbhome_1\BIN >LSNRCTL.EXE stop 

To start  listener
D: \oracle\app\product\11.2.0\dbhome_1\BIN >LSNRCTL.EXE start

Alternatively in windows run write services.msc and press enter then find the oracle listener service and right click on the service name (OracleOraDb11g_home1TNSLinstener) then choose restart.

Step 5: Creating DB Link in Oracle and accessing data from MS Access database

You need to create a database link(DBLink) in your oracle database. Create DB Link command
CREATE public DATABASE LINK accessdblink USING 'accessdb';

Note: Here accessdblink is the name of link we creating and accessdb is the name of access DSN

Query the database:
Select * from empmas@accessdblink;

Last Update: Tuesday 27 March, 2018

Previous Update:Wednesday 02 August, 2017