Connecting Oracle with 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.

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.

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_HOMEhs 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:OracleOra101) (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_HOMEhsadmininithsodbc.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.

[@more@]