Martin Reid
mwp.reid at qub.ac.uk
Thu Apr 23 09:17:45 CDT 2009
Rocky
http://www.orafaq.com/node/60
martin
Martin WP Reid
Information Services
Queen's University
Riddel Hall
185 Stranmillis Road
Belfast
BT9 5EE
Tel : 02890974465
Email : mwp.reid at qub.ac.uk
________________________________________
From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin [rockysmolin at bchacc.com]
Sent: 23 April 2009 15:06
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: Error Message while Connecting Access to Oracle
Thanks Jim. This is an area I know nothing about so I'm pretty much unable
to help the client. But I think a sale may hang in the balance. Will
forward to the client.
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hewson, Jim
Sent: Thursday, April 23, 2009 6:48 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: Error Message while Connecting Access to Oracle
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.
############################################################################
####
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com