Cannot See Table in MS ACCESS Using Oracle HS and Database Link
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.
- As stated, I do not have MS ACCESS installed on my work PC and will likely not be able to have it installed.
- The database is part of a third party application so I'm not privy to the database design.
- 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_at_MS_ACCESS_DB;
17-MAR-09 SQL> select count(*) from some_table_at_MS_ACCESS_DB; select count(*) from some_table_at_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).
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. Received on Tue Mar 17 2009 - 16:51:43 CDT