HOME

Configure DB link to MS Access from Oracle

Contact Me

Configure DB link to MS Access from Oracle

This document gives step by step instructions to set up a Database link in Oracle database to access MS Access database (*mdb/*accdb).

Environment used:

1) Create an ODBC Data Souce Name (DSN), pointing to MS Access (*mdb) file that you need to access. Make sure to have 64 bit MS Access driver if on a 64 bit machine, and create DSN using 64 bit ODBC (i.e. "% WINDIR %\system32\odbcad32.exe").

Otherwise, following error is likely to occur:

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application {IM014} error 

[Note: On 64-bit systems the version of odbcad32.exe in %WINDIR%\SysWow64 is 32-bit and accesses 32-bit ODBC drivers. If the ODBC DSN has been created using "%WINDIR%\SysWow64\odbcad32.exe" so the ODBC driver used in the DSN is 32-bit. On 64-bit systems the version of odbcad32.exe in %WINDIR%\System32 is 64-bit and accesses 64-bit ODBC drivers. If the ODBC DSN has been created using "%WINDIR%\System32\odbcad32.exe" so the ODBC driver used in the DSN is 64-bit.]

While creating DSN (see following screenshots), mention the mdb file name you want to access. Say DSN is odbc1.

connect_msaccess_odbc1.JPG      	connect_msaccess_odbc2.JPG
			


2) Update listener.ora taking following entry as example:

				  (	
					  SID_DESC =
					 (SID_NAME = odbc1)
					 (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
					 (PROGRAM = dg4odbc)
				   )
			

The Oracle Database Gateway for ODBC (DG4ODBC) allows you to build heterogeneous solutions that transparently integrate ODBC databases with Oracle.

In 11g, this gateway is called as DG4ODBC, prior to that it was hsodbc. dg4odbc is available in ORACLE_HOME\BIN of Oracle Enterprise edition.

The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle listener.ora.

3) Update tnsnames.ora taking following entry as example:

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

Here we have actually added a connect descriptor for the gateway. The connect descriptor identifies the Oracle server to attach to and the SID_NAME to use.


4) Add the following lines to new file, %ORACLE_HOME%\hs\admin\initodbc1.ora:

	HS_FDS_CONNECT_INFO = odbc1 
	HS_FDS_TRACE_LEVEL = OFF
	HS_FDS_SUPPORT_STATISTICS=FALSE 

	-- HS_FDS_CONNECT_INFO: The name of the system ODBC data source that you added in the Windows ODBC Administrator.
	-- HS_FDS_SUPPORT_STATISTICS: Specifies the level of tracing. If set to "Debug". This setting generates a log file for each SQL*Plus connection. 
	--Gateway log files are stored in $ORACLE_HOME\hs\trace folder. 
			

5) Restart the listener. services command in lsnrctl will show new service, odbc1. Use tnsping to make sure that you can connect to the new service.

6) Create a DB link in the local 11g R2 DB that you have :

CREATE DATABASE LINK "odbc_1"   USING 'odbc1';

7) That's it, you are good to go:

select * from exempt@odbc_1;
					connect_msaccess_result.JPG
			
Thanks...

TOP