Access/SQL Server with Oracle Linked server

[schtroumfps]

Hi,
I would like to connect a couple of table from a Oracle 9.2 Server into
my Access XP project file (adp extension). That Access front-end file
is linked to my SQL Server Database back-end.
To connect the table from Oracle I have created and configured a .udl
file with the Oracle Provider for OLE DB and all the necessary
information (Username and Password). The connection has succeeded.
I can see the server, tables and views from the Oracle database in the
SQL Enterprise Manager and in my Access adp assistant but as soon as I
am terminating to link the tables with the Access assistant for linked
server I am getting that message : OLE DB Provider OraOLEDB.Oracle.1
reported an error : Provider caused a server fault in an external
process.
I have tried to connect to the Oracle database with the Microsoft OLEDB
Provider for Oracle but I am getting that message at the exact same
point in the procedure of connection : 'OLE DB Provider
'myserver' does not contain the table 'mytable'. The table
either does not exist or the user does not have the permissions on that
table. The connection has succeeded with the current username and
password, I do not know why it will block me at this point?
Anyone knows the solution for my problem?

 

[Albert D.Kallal]

ADP, because they use specific sql features do not work with anything else
but sql server.

You can write code, and likely open some tables in ADP using code, but that
stuff will not work for the user interface.

ADP's of course also allow table design changes etc, but in fact when you do
this, specific DDL (data definition language) commands are sent to the SQL
server. Because there is no agreement, and no standard DDL between the
vendors of sql products, then it not really possible to build a product that
allows table design changes when using different servers. (further, many
database servers don't have certain features like enforced integrity, or
cascade updates). ADP's because they so tightly work (seamlessly) with sql
server, don't work correctly with anything else...

You will have to use a mdb, and linked tables if you need to "mix" your data
sources. Of course, when using a mdb file with oracle, or sql server, you
have to use the "vendors" tools to modify the table designs (enterprise
manager in sql server, and whatever oracle gives you). You can rapidly see
that using a mdb approach means that the "vendors" have to write the system
that manages the tables on the server side...not the mdb side.

ADP's of course have their own sql server manager, and thus you don't have
any need for the EM tools that ms's provides.

 

[david epsom dot com dot au]

As I understand it, you can create a
connection to Oracle, but when you try
to use the connection you get a permissions
error.

That means you don't have permission.
Check your Oracle database.

To use Oracle through SQL Server, you
will also probably need to enable
cross-database chaining.

(david)