Custom Search . . .

Tuesday, July 7, 2009

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


5 comments:

SoSoo said...

Dear Babu,

Really that is such a great work.
I make a test case and i worked but i have the same issue but the oracle database installed at Linux server and Ms-Access installed at Windows server
And when try to do the same thing it did not work
so kindly can you tell me if it is applicable to done or not

Babu said...

It'a applicable, what's your prob

SoSoo said...

My problem is it did not work between the two servers (Linux and Windows)and i got an error

I am so confused between steps on Linux and steps on Windows

Can you tell me steps to do on the both servers as you did in your post

Thanks a lot

Babu said...

Ok.. What error your getting?

Can I have the details to babu.knb@gmail.com

SoSoo said...

Ok.. i sent the error
Thanks