Version of Oracle: 11g -> 11.2.0.1.0
Version of Windows: Windows Server 2008
Steps to complete:
- Create System DSN with Microsoft Access Driver
- Modify listener.ora file
- Create the file initaccess.ora
- Modify tnsnames.ora file
- Restart listener
- Check connection with access database
- Create the DB Link in Oracle
STEP 1: Create system DSN
First go to ODBC Data Source Administrator click on System DSN tab. The create a DSN with the following parameter (you can choose the name link that you prefer but then you need to keep the same name all over the process).
In our case:
STEP 2: Modify listener.ora file
Let's locate the file. In our case:
- E:\app\Administrador\product\11.2.0\dbhome_1\NETWORK\ADMIN
We need to add the following text at the end. By the way, look at I posted as 'oracle_home' the directory in C: drive. That's because I had more than one directory related to Oracle, and I still don't understand it but worked in my case. Maybe in yours there's only one.
Parameters you need to modify:
In first block:
- SID_NAME: name chosen in the step above. The name of the dsn created ('access' in our case)
- ORACLE_HOME: Home directory of Oracle
- PROGRAM = dg4odbc (I think that from Oracle 11g and beyond, 'dg4odbc' is mandatory.
In second block:
- HOST: The name of the hostname (not sure if localhost' or an IP address are supported, but I guess they are)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = access)
(ORACLE_HOME = C:\app\Administrador\product\11.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
)
)
Make sure there is no space before 'SID_LIST_LISTENER' or 'LISTENER' in the file you modify:
STEP 3: Create the initaccess.ora file
Go to:
- E:\app\Administrador\product\11.2.0\dbhome_1\hs\admin
There, make a copy of 'initdg4odbc.ora' file (I deleted it after I made a copy). Add the following text to the file:
HS_FDS_CONNECT_INFO = access
HS_FDS_TRACE_LEVEL = 0
- HS_FDS_CONNECT_INFO: Here we write the name of the link created in the ODBC source tool in the first step, so it's 'access'
- HS_FDS_TRACE_LEVEL: Leave it with a 0
STEP 4: Modify tnsnames.ora file:
Let's locate the file. In our case:
- E:\app\Administrador\product\11.2.0\dbhome_1\NETWORK\ADMIN
Parameters to modify:
- HOST: hostname of the server
- SID: this is the name of the 'initaccess.ora' file BUT taking away the 'init' part and '.ora' file extension: then 'access'.
- (HS=OK) = don't forget this.
access =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host_name) (PORT = 1521))
(CONNECT_DATA =
(SID = access)
)
(HS=OK)
)
Be sure that you don't leave spaces before the 'access =' parameter.
STEP 5: Restart the listener
- Open a command line terminal
- Go to: C:\app\Administrador\product\11.2.0\dbhome_1\BIN
- Stop the listener
- C:\app\Administrador\product\11.2.0\dbhome_1\BIN>LSNRCTL.EXE stop
- You should see a message saying it was successfull
- Restart the listener
- C:\app\Administrador\product\11.2.0\dbhome_1\BIN>LSNRCTL.EXE start
- You should see a message saying it was successfull
- You should also see, (in the output, when restarting the server) a message talking about the 'access' service we just added
El servicio "access" tiene 1 instancia(s).
La instancia "access", con estado UNKNOWN, tiene 1 manejador(es) para este ser vicio...
El comando ha terminado correctamente
STEP 6: Test connection with the access database
- Open a command line terminal
- Go to: C:\app\Administrador\product\11.2.0\dbhome_1\BIN
- Run tnsping.exe (as argument, pass the name of the link: 'access'):
- C:\app\Administrador\product\11.2.0\dbhome_1\BIN>tnsping.exe access
- You should see a message saying it was correctly run and displaying the delay in msecs
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-JUN-2
014 11:04:35
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Archivos de parßmetros utilizados:
E:\app\Administrador\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Adaptador TNSNAMES utilizado para resolver el alias
Intentando contactar con (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = CUPCA
KE) (PORT = 1521)) (CONNECT_DATA = (SID = access)) (HS=OK))
Realizado correctamente (20 mseg)
STEP 7: Create the DB Link in Oracle and query the Ms Access database:
Create the DB Link (always as **sys):**
CREATE public DATABASE LINK accessdblink USING 'access';
* 'access': Same name as in tnsnames.ora -> 'access = '
Query the database:
SELECT * FROM table_name@accessdblink;
Source:
http://stackoverflow.com/questions/24081001/how-to-query-an-external-ms-access-db-from-an-oracle-db
