How to Connect MS-Access with Oracle Database

How to Connect MS-Access with Oracle Database

You can configure heterogeneous service to allow an oracle database to connect with Microsoft Access database from about any platform Linux or Windows.

ODBC stands for Open database connectivity is an interface written in C program. Oracle ODBC driver provides a standard interface that allows one application to access many different data sources.

Differentiate between ODBC and Net8?

ODBC is a database independent where as NET8 is database dependent.

ODBC is used to connect to any database that is supported by SQL where as Net8 is an oracle protocol, is used to connect only oracle database.

ODBC uses drivers for connecting to different database where as NET8 uses network listener for connecting to oracle database.

What are requirement to work on ODBC?

–        A client software such as SQL*Net or Net8 on the client machine. However a third party solution OCI driver already includes within the driver thus no need oracle Net installation.

–        A TCP/IP connection is required.

How to attached oracle table in MS-Access?

–        Start the linking process by either selecting File–>Get External data–>Link Table or right click on within the table list and select link table.

–        Select ODBC from the drop down list.

–        Then from Machine data source tab select the appropriate data source name and click OK. This will open a login dialog box.

–        Once we login successfully onto oracle, a list of table displayed.

–        Now you are able to use this table in the MS-Access. 

Prepare MS-Access Environment:

Create a database in MS-Access and create at least one table in this database. While creating the database ensure *.mdb file is used to save the Access database. For Example: you can see in the below picture a table named ‘shahid’ is created under the database db1.

Define ODBC Connectivity:

Open the utility Microsoft ODBC Administrator and click on ‘Add’ button to add a new data source by providing description, service name and user ID & password. Generally if you expand the Start->Programs->ORACLE-HOME->Configuration and Migration tools path, you will find this utility. Finally test your connection before moving to next step.

Prepare the Oracle Environment:

Configure the Oracle Listener on the Windows machine. Add the following entry in LISTENER.ORA and TNSNAME.ORA

 (SID_LIST =

  (SID_DESC =

   (SID_NAME = hsodbc)

   (ORACLE_HOME = D:\Oracle\ora92)

   (PROGRAM = hsodbc)

  ))

ACCESS_DB.WORLD =

  (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = DBSERVER) (PORT = 1521)

  )

  (CONNECT_DATA = (SID = hsodbc)

  )

  (HS=OK)

 )

Ensure to check the tnsping for new entry and if needed you can start/stop listener from the command line:

C:\>tnsping access_db
C:\>lsnrctl stop
C:\>lsnrctl start

Configure Oracle HS: Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name.

HS_FDS_CONNECT_INFO = odbc1

HS_FDS_TRACE_LEVEL = off

Connect Oracle with Sys user and execute this script.

SQL>@D:\oracle\RDBMS\ADMIN\caths.sql

SQL> select * from SYS.HS_FDS_CLASS;

FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID

-------------  ------------------ -------------

BITE Built-In Test Environment 1

Create a database link to access from Oracle database

SQL> CREATE DATABASE LINK access_db USING ‘access_db.world’; 

Database link created.

The tables in the MS-Access database can now be accessed from the Oracle environment.

SQL> SELECT * FROM shahid_table@access_db;

        ENO ENAME        SALARY     ADDRESS

 ---------- ------------ --------   ---------------------

         1 shahid       6000       Riyadh

         2 xyz           5000       Delhi

         3 pwz          4000       Banglore

         4 bwz          3000       Mumbai

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

Table created.