How to Connect Oracle Database with Microsoft Access Database?

As salamualikum (islamic greetings), brothers and sisters. :)

Hope you are well with the grace of Almighty Allah (swt) but stuck in a place when you try to connect oracle database with MS Access database.

In short, you are trying to access MS Access data from Oracle DB.

Lets START…

This connectivity calls heterogeneous connectivity.

Important: The init-parameter GLOBAL_NAMES should have value FALSE. Check this parameter first before continuing the procedure.

In this example, I uses,

Database Name: TESTDB

OS User                :Administrator

OS password     :admin123

Setp 1 – Create Database Link via system DNS

Important: use 32-bit odbc driver for 32-version of oracle database and 64-bit odbc driver for 64-bit oracle database.

A 64-bit version of the Microsoft Windows operating system includes the following versions of the

Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):

  >>  The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

  >>  The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

           

 if odbc driver not found on the odbc list then click at  C:\Windows\SysWOW64\odbcad32.exe

(32-bit) for 64-bit download link are

 http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

1. On the Oracle server start the ODBC Data Source Administrator. This can be done by using the run command: go to start/run and type odbcad32 and press enter.
2. In the ODBC Data Source Administrator window go to the System DSN tab and click on Add...
3. Choose in the Create New Data Source window the Microsoft Access Driver (*.mdb) and click on Finish.
4. In the ODBC Microsoft Access Setup window enter the data source name (e.g. TESTDB), the description (e.g. Test database) and select the Access database
1. If the Access database is on the same machine as Oracle is running, you can ignore step 1.5 and step 2!
5. If the Access database is on another machine in the network, you should use UNC-path notation. In the Map Network Drive window, leave the drive-letter on (none) and select via the UNC-path (e.g. \\192.168.100.10\HAMS_2013\HAMS_2013.mdb) the Access database. Finish the creation of the ODBC Link and close all the ODBC windows.

Step 2 Create Network ID

In order to perform a good connection to the Access database on another machine, you need to follow the following steps:

1. Create on the network and user id (e.g. TestDbAdmin) and give this UID read rights on the location of the Access files. NOTE: use the appropriate rights, this depends on the actions you want to take on the Access database.
2. Place this user, TestDbAdmin, in the ORA_DBA group on the Oracle server.

OR

If you want to use Administrator user account. Go to windows service.  Change the Database and TNS service Log On As administrator.

Right click on a services  >> go properties >> go Log On and use this account.

Restart both the services.

Step 3 Create the Oracle TestDb listener (Oracle HS)

Important: Oracle Database Version 10g (known as inithsodbc.ora), Version 11g (known as initdg4odbc.ora)  

Ø  10g file location: ORACLE_HOME\hs\admin\inithsodbc.ora

Ø  11g file location: ORACLE_HOME\hs\admin\ initdg4odbc.ora

Open the file at notepad and, modify the HS init parameters

HS_FDS_CONNECT_INFO = <odbc data_source_name>

HS_FDS_TRACE_LEVEL = <trace_level>

TO

HS_FDS_CONNECT_INFO = TESTDB ( created at setp 1)

HS_FDS_TRACE_LEVEL = OFF

DATABASE LISTNER Configuration

 Open the file listener.ora (%oracle_home%/network/admin) and add the following text, save and close the file (e.g. 11g database listener)

Original configuration

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = CLRExtProc)       (ORACLE_HOME = C:\Oracle\app\Administrator\product\11.2.0\dbhome_1)       (PROGRAM = extproc)       (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")     )  ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )   ) ADR_BASE_LISTENER = C:\Oracle\app\Administrator

Modified Configuration

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = CLRExtProc)       (ORACLE_HOME = C:\Oracle\app\Administrator\product\11.2.0\dbhome_1)       (PROGRAM = extproc)       (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")     )     (SID_DESC =       (SID_NAME = dg4odbc)       (ORACLE_HOME = C:\Oracle\app\Administrator\product\11.2.0\dbhome_1)       (PROGRAM = dg4odbc)     )  ) LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )   ) ADR_BASE_LISTENER = C:\Oracle\app\Administrator

Stop and start the listener service or from the command line:
C:\> lsnrctl stop
C:\> lsnrctl start

DATABASE TNSNAMES Configuration

Open the file tnsnames.ora (%oracle_home%/network/admin) and add the following text, save and close the file.

TESTDB =   (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost) (PORT = 1521)   )   (CONNECT_DATA =       (SID = dg4odbc)   )   (HS=OK)  )

Final Step- Create Oracle Database Link to TESTDB

Connect to database as SYS user or any user having create database link privilege.

CREATE PUBLIC DATABASE LINK “TESTDB”    
CONNECT TO "Administrator"      --- OS user name
IDENTIFIED BY "admin123"           --- OS password
USING 'TESTDB';

Test the Link

select * from dual@"TESTDB";
If you got a number of rows back, you're done!
Reference.
### http://adf.ly/tm6Da
### http://adf.ly/tm6NA
Metalink Note

1. Document 1195583.1

2. Document 1475507.1

3. Document 234517.1

If it helps, leave your message.

Praise to Almighty Allah(swt). 

Fi amanillah