How to query/fetch data of MS Access DB from an Oracle DB?
- Oracle database 11g version 220.127.116.11.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 DriverFirst 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
Step 2: Configuring Oracle Net Listener-listener.ora fileLet's locate the file; it is in database home/network/admin directory. In my case: it is
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
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.oraTNS 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 databaseYou 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
All post under category Oracle
1. How to convert number into word/text with PL/SQL
2. An Overview and Clear Understanding of Oracle REF CURSOR
3. How to query/fetch data of MS Access DB from an Oracle DB?
4. How to install Apex5 with Oracle REST Data Services in windows
5. How to Restore Deleted Rows in Oracle | Data Recovery
6. How to run an oracle job from windows batch file
7. How to convert | export oracle matrix report into M.S Excel file, oracle 11g