Hewson, Jim
JHewson at nciinc.com
Thu Apr 23 08:47:53 CDT 2009
I'm not sure if this will be much help.
But, when I had to connect to Oracle the only way I could get Access to
connect through ODBC was to have a full install of Oracle on my machine.
Otherwise, I got messages that they didn't play friendly as your client
experiences. Another thing, I learned was that if there was any remnant
of the failed ODBC connection it would try and use it instead of
creating a new one.
HTH.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, April 23, 2009 8:35 AM
To: 'Access Developers discussion and problem solving'
Subject: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
################################################################################
If you have received this message in error, please contact the sender
immediately and be aware that the use, copying, or dissemination of
this information is prohibited. This email transmission contains
information from NCI Information Systems, Inc. that may be considered
privileged or confidential and is intended solely for the named
recipient.
################################################################################