Connecting Oracle with MS-Access

articles: 

This article shows how Oracle's Heterogeneous Services can be configured to allow a database to connect to a Microsoft Access database using standard databases links. The method described can be used to connect to MS-Access from about any platform - Unix/ Linux or Windows.

MS-Access 2003 and Oracle 10g Release 1 are used to illustrate the concepts. However, this procedure should work with Oracle 8i, 9i, 10g, 11g and 12c databases, as well as various versions of MS-Access.

Step 1: Prepare the MS-Access environment

If you do not have a MS-Access environment, start by installing the required software and create a test table.

Create a Table using Microsoft Access

Step 2: Define ODBC connectivity

Use the ODBC Administrator Utility to define a local System DSN that can be used to connect to the Access database (same machine). Ensure that the correct *.MDB database file is selected.

Use the Microsoft ODBC Administrator Utility to define local connectivity

Step 3: Prepare the Oracle Environment

Install the Oracle Database Server software on the same machine where MS-Access is installed.

NOTE: It is not sufficient to only install Client Software, as we require an Oracle Net Listener and the Heterogeneous Services (ORACLE_HOME\hs directory) software to be installed as well.

Step 4: Configure and Start the Oracle Listener

Configure the Oracle Listener on the Windows machine. Here is a sample LISTENER.ORA entry that can be used. Change the HOST, PORT and ORACLE_HOME entries to match your setup. You may also use a different SID_NAME if required.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = my_windows_machine_name)(PORT = 1521)) 
      )
    )
  )
SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = hsodbc)
   (ORACLE_HOME = c:\Oracle\Ora101)
   (PROGRAM = hsodbc)
  )
)

Stop and start the listener service or from the command line:
C:\> lsnrctl stop
C:\> lsnrctl start

Step 5: Configure Oracle HS:

Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name (ODBC1 in our case as defined in step 3).

HS_FDS_CONNECT_INFO = odbc1 
HS_FDS_TRACE_LEVEL = off

Note: If you used a custom SID_NAME in step 4, name the file accordingly - INIT.ORA.

Step 6: Configure Oracle connectivity to Windows Machine

From now on we are going to work on the Oracle Server (Unix or whatever you run) add the following TNSNAMES.ORA entry:

access_db.world =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.31) (PORT = 1521)
  )
  (CONNECT_DATA =
      (SID = hsodbc)
  )
  (HS=OK)
 )

Ensure you can tnsping the new entry before continuing.

Step 7: Create a database link

Create a database link using the entry defined in step 6.

SQL> CREATE DATABASE LINK access_db USING 'access_db.world';  

Database link created.

The tables in the access database can now be queried from the Oracle environment.

SQL> SELECT * FROM my_access_tab@access_db;

        ID Field1               Field2
---------- -------------------- --------------------
         1 row1col1             row1col2
         2 row2col1             row2col2
         3 row3col1             row3col2

SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM my_access_tab@access_db;

Table created.

Additional Reading:

1. Oracle Heterogeneous Connectivity Administrator's Guide.

2. Oracle Metalink Note 109730.1 - How to setup generic connectivity (Heterogeneous Services) for Windows NT.

Comments

I was trying to access non-oracle database from oracle.

But this note is very helpful.

I've read so many notes from so many sites.

I tested the connection and it is working fine.

Once more thanks a lot.

Regards
Salih KM

Thanks a lot for this. I have been trying to make this work but missed out step 7 - creating the database link. It now works fine.

Hi

It seems a nice article.
But, can we connect oracle forms builder with non oracle database
like MS SQL Server 2000 or MS Access.

If yes the how...

Regards
R K Sharma

Hi

I have Oracle client only. Is it possible to connect MS-Access through Oracle Forms.

Regards
S. Kolappan.

Hi,

Kindly follow the link to check the steps to connect Oracle Forms to MSACCESS.

http://gskaushik.blogspot.com/2009/05/oracle-forms-conection-to-msaccess.html

We are getting ORA-28500 that is data source connectivity problem. Can someone help?

Hello,

The link is functioning just fine. Though, I have one issue: it is about the speed of this link.
I am using MS Access 97 and Oracle 10g. For about 70 000 records it takes more than 1h... Moreover it blocks the destination table in Oracle. So, if I have 12 this kind of links it will last more than 1 night... :(

I even increased the bandwidth from 512 to 1024 kbps... no significant increase.

Do you have any ideas?

Thank you!

Kind regards,

smajoka's picture

I found this error message while querying the access database.

ORA-28545: error diagnosed by net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from  ACCESS_DB

Listener.ora:

SID_LIST_LISTENER=
(SID_LIST =
  (SID_DESC =
   (SID_NAME = odbc1)
   (ORACLE_HOME = D:\app\win7-64-1\product\11.2.0\dbhome_1)
   (PROGRAM = hsodbc)
  )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 134.132.109.157)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\win7-64-1

Tnsnames.ora:

access_db.world =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 134.132.109.157) (PORT = 1521)
   )
   (CONNECT_DATA =
       (SID = odbc1)
   )
    (HS=OK))

Could anyone help me to resolve this issue?

Many thanks in advance.

Shafqat

Thank you man.

It works just fine.

Ahmed.