Cannot See Table in MS ACCESS Using Oracle HS and Database Link
- From: "Langston, Chris" <Chris.Langston@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Mar 2009 16:51:43 -0500
Hello Wizards of the Oracle-L list.
I'm facing an issue of accessing data from an MS ACCESS database from an Oracle
10gR2 instance. I've successfully been able to setup Oracle XE (to use the
Oracle Net Services) on my local PC and a database link from our Oracle
instance residing on a Solaris 10 server. I do not have MS ACCESS on my PC but
was able to locate an mdb file for testing purposes (this is all being setup as
a proof-of-concept for our customers) and create a database link to access a
table using Oracle heterogeneous services. So far, so good. Everything worked
as documented. The issue is when I point the ODBC to a copy of a customer
supplied mdb file. I know the table I queried exist and I can import it into an
Excel spreadsheet. I can query it using SQL Developer. I can select from dual
(see below). But I get an error when I try to select from the same table I can
import into Excel or query using SQL Developer.
Some constraints.
1. As stated, I do not have MS ACCESS installed on my work PC and will
likely not be able to have it installed.
2. The database is part of a third party application so I'm not privy to
the database design.
3. I've been told by a member of the third party application that
database security is built into the application itself and not the database.
That's what I've been told.
(Names have been changed to protect the guilty. These two statements worked
when the ODBC connection pointed to another mdb file so I suspect the problem
might be with the database link (heterogeneous service) and some MS ACCESS
database security setting.)
SQL> select sysdate from dual@MS_ACCESS_DB;
SYSDATE
---------
17-MAR-09
SQL> select count(*) from some_table@MS_ACCESS_DB;
select count(*) from some_table@MS_ACCESS_DB
*
ERROR at line 1:
ORA-00942: table or view does not exist
[Generic Connectivity Using ODBC][Microsoft][ODBC Microsoft Access Driver] The
Microsoft Jet database engine cannot find the input table or query 'SOME_TABLE'.
Make sure it exists and that its name is spelled correctly. (SQL State: S0002;
SQL Code: -1305)
ORA-02063: preceding 2 lines from MS_ACCESS_DB
I've searched then Net, OTN and Metalink and have found lots of good
information on Oracle and Access but nothing so far that addresses this
specifically. One article mentioned using MS ACCESS to export/import the
database into a newly created database to remove the security settings, but as
stated in the constraint, I do not have MS ACCESS installed and neither does
anyone else around me.
Any insight or link to some documentation would be most appreciated. It's
nearly 5:00 PM now (quittin' time) and I'm heading out of the office for the
day so an immediate answer is not required. I'll check back first thing
tomorrow morning (3/18).
Chris Langston
________________________________
NOTICE: This email and any attachments are for the exclusive and confidential
use of the intended recipient(s). If you are not an intended recipient, please
do not read, distribute, or take action in reliance upon this message. If you
have received this in error, please notify me immediately by return email and
promptly delete this message and its attachments from your computer system.
Other related posts:
