Oracle Database Link to MS Access

(Originally posted at May 19th, 2008 by Robje)

In this post, I describe how you can make an Oracle database link to an MS Access database. When done, you can query from an oracle database onto an Access database. But with some little changes, you can do the same trick to a SQL Server database or even a database type you like (to which you can connect by ODBC).

Important: The init-parameter GLOBAL_NAMES should have value FALSE. Check this parameter first before continuing the procedure.

In this example, I uses TESTDB as database name, replace this with your database/name/user id. If I mention %oracle_home%, I mean the Oracle Home directory. This example is worked out on an Oracle 8.1.7.4 installation, but I suppose it will work on higher Oracle versions too.

1. Create a new ODBC link

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

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:

1. Create on the network an 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.
2. Place this user, TestDbAdmin, in the ORA_DBA group on the Oracle server.

3. Create the Oracle TestDb listener

In these steps, some Oracle files will be created and/or modified. A new listener will be created, which will take care of the connection

1. Open the file inithsodbc.ora (%oracle_home%/hs/admin) and save this as initTESTDB.ora. Edit the content so it looks like:

# 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 = HORSODBC
HS_FDS_TRACE_LEVEL = OFF
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

2. Open the file listener.ora (%oracle_home%/network/admin) and add the following text, save and close the file:

LISTENERTESTDB =     
  (ADDRESS_LIST=      
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))      
    (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))      
  )      
SID_LIST_LISTENERTESTDB=      
  (SID_LIST=      
    (SID_DESC=      
      (SID_NAME=TESTDB)      
      (ORACLE_HOME = C:\oracle\ora81)      
      (PROGRAM=hsodbc)      
    )      
  )

3. 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 = 1522))      
    (CONNECT_DATA =      
      (SID = TESTDB)      
    )      
    (HS = OK)      
  )

4. Check the file sqlnet.ora (%oracle_home%/network/admin) if the following setting is correct (correct if it differs):

SQLNET.AUTHENTICATION_SERVICES= (NONE)

5. Start the new TESTDB listener with the following command (on the command line tool):

NOTE: DON'T USE A RDP (REMOTE DESKTOP) CONNECTION! Because of windows credential error. Apply this directly on the Oracle machine or via e.g. VNC Viewer connection.

Orahome\bin\lsnrctl start listenerTestDb

Starting the new listener can produce an error message, because the services uses the default logon settings (system account).

6. Configure the ListenerTestDb:

• Open the properties panel of this service (can be found in the windows services list at the administrative tools)
• Stop the service (if it is running) and edit the log on credentials to the user account earlier made (e.g. TestDbAdmin)
• (Re)start the service.

7. Test the listener with the following statement:

%oracle_home%\bin\tnsping testdb

If the test succeeded, the database link in Oracle can be made.

4. Create Oracle Database Link to TestDb

1. Create database link to TESTDB (note: credentials SYSTEM/MANGER can be changed to your configuration):

CREATE PUBLIC DATABASE LINK “TESTDB”     
CONNECT TO "SYSTEM"      
IDENTIFIED BY "MANAGER"
USING 'TESTDB'

2. Test the link with a count(*) query to a table on the TESTDB database.

If you got a number of rows back, you're done!

(with thanks to my colleague Frank den Outer)