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
- 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.
- In the ODBC Data Source Administrator window go to the System DSN
tab and click on Add...
- Choose in the Create New Data Source window the Microsoft Access
Driver (*.mdb) and click on Finish.
- 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
- If the Access database is on the same machine as Oracle is
running, you can ignore step 1.5 and step 2!
- 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:
- 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.
- 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
