[AccessD] FW: Error Message while Connecting Access to Oracle

Dear List:
 
Does anyone have experience connecting Access to Oracle?  This client is in
Bahrain so it's a bit awkward.  My client's email and the programmers email
with the error she got are below. Is there something obvious that they're
doing wrong?  Or any ideas what they might try to make the connection.  The
back end that they're trying to connect to is an Access 2000 mdb.

MMTIA

Rocky

 
Minus the diagrams, here's the steps through step 7 from the document the
programmer's using to make the connection:
 
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 and 10g 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. 

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_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 at 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 at access_db;

Table created.


From: steve at bfginternational.com [mailto:steve at bfginternational.com] 
Sent: Wednesday, April 22, 2009 11:45 PM
To: 'Rocky Smolin'
Subject: FW: Error Message while Connecting Access to Oracle


 
Hello Rocky,
 
We've had no luck connecting Oracle8i to Access 2003.  Sudha, the
programmer, has followed the attached guide but received the error message
below once step 7 is reached.  She's defined the Access ODBC connection as a
User DSN to the ezmrpwdg-V22.mdb.  She's also updated 2 separate TNS names
files.
 
Perhaps you've had previous customers perform this task?  Thoughts
appreciated.
 
Regards,
 
Steve
 

________________________________

From: sudha at bfginternational.com [mailto:sudha at bfginternational.com] 
Sent: Thursday, April 23, 2009 8:05 AM
To: steve at bfginternational.com
Subject: Error Message while Connecting Access to Oracle



ORA-28500: Connection from ORACLE to a non-Oracle System returned this
message;

[Generic Connectivity Using ODBS][Microsoft][ODBC Driver Manager] Data
Source Name not found and no default driver specified (SQL State: IM002; SQL
Code: 0)

ORA-02063: Preceding 2 lines from ACCESS_DB

 

Regards,

 

Sudha Teki

Software Engineer 

MIS Department

BFG International W.L.L