How to Connect Oracle to Ms-Access

This document will explain how to connect Oralce to Non Oracle database it’s called heterogeneous database.

Here I tried Oralce to Ms-Access connectivity.

Pre-Request:

1. Create one blank database from MS-Access & create one sample table in your blank database. Table called “ms_emp”

2. Save your ms-access database called “msdevdb.mdb”

3. Create System DSN; to access msaccess from oracle. System DSN name called “msdb”

3.1 Make sure; “HS_FDS_CONNECT_INFO” this should be marked to odbc name. from ORACLE_HOME\hs\admin\initodbc.ora

SQL> host type F:\oracle\product\10.2.0\db_1\hs\admin\inithsodbc.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for an ODBC Agent.

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = msdb

HS_FDS_TRACE_LEVEL = off

#

# Environment variables required for the non-Oracle system

#

#set =

4. in LISTENER.ORA just add the below entry’s

(SID_DESC =

(SID_NAME = hsodbc)

(ORACLE_HOME = f:\oracle\product\10.2.0\db_1)

(PROGRAM = hsodbc)

)

5. In TnsNames.ORa Just add the below entry

msaccess =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

(CONNECT_DATA=(SID=hsodbc))

(HS=OK)

)

6. Conn oracle database using Sys user. Execute the below script in Sys user.

SQL> @F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\caths.sql

SQL> select * from SYS.HS_FDS_CLASS;

FDS_CLASS_NAME FDS_CLASS_COMMENTS

FDS_CLASS_ID

------------- - -------------------- --------------------------------------------------------

BITE Built-In Test Environment 1

SQL> host tnsping msaccess

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 08-JUL-2009 09:42:01

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

f:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=hsodbc)) (HS=OK))

OK (50 msec)

7. Create database disk to access from oracle database.

SQL> create database link msaccess using '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=hsodbc)) (HS=OK))';

Database link created.

SQL> select * from ms_emp@msaccess;

select * from ms_emp@msaccess

*

ERROR at line 1:

ORA-02085: database link MSACCESS connects to HO.WORLD

SQL> show parameter global_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

global_names boolean TRUE

SQL>

SQL> alter system set global_names=false ;

System altered.

SQL> select * from ms_emp@msaccess;

eno name

---------- --------------------------------------------------

10 babu

20 taj

SQL> select * from SYS.HS_FDS_CLASS;

FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID

------------------------------ ------------------------------ ------------

BITE Built-In Test Environment 1

ODBC10.2.0.4.0_300 self-registered FDS class 21

ODBC self-registered FDS class 22

feel free to post your comments/feedback.

thanks