How to query an external MS Access DB from an Oracle DB?

Question

How to configure Oracle to be able to query a MS Access database (.accdb) that is lcoated in the same server (localhost)?

Which steps to follow?

Answer 1

Version of Oracle: 11g -> 11.2.0.1.0

Version of Windows: Windows Server 2008

Steps to complete:

1. Create System DSN with Microsoft Access Driver
2. Modify listener.ora file
3. Create the file initaccess.ora
4. Modify tnsnames.ora file
5. Restart listener
6. Check connection with access database
7. 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:

• Driver: Microsoft Access Driver (*.mdb,*.accdb)
• Name: access
• Description: (doesn't matter)
• Database -> Select -> (Select the .accdb or .mdb database)
• Save the configuration

• Make sure you see this listed once you save it:

  Name - Driver

  access - Microsoft Access Driver (*.mdb,*.accdb)

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;