The application part of my database is in Access 2003, and I use tables that are linked from SQL server. Now, I have some tables that I have to link from an Oracle database. I link them through and ODBC connection and it works fine. Is it possible to link that Oracle link in SQL and then link that table as it is already linked in Access 2003? So, I want to use just one ODBC connection to SQL server and in SQL Server link that Oracle link.
+3
A:
Yes, I believe the double indirection structure you suggest should work "OK". That's because MS_SQL linked server sources are handled very much like local databases and can be queried individually, i.e. within queries not involving local databases.
Do note, however, that it could be much less efficient, since you introduce an extra "hop". Also, do look for possible issues with regards to type mapping as some types in oracle may get mapped to a slightly different type in SQL than when accessed directly from MS-Access. Such type mapping issues would however be easy to work-around.
Edit: To "establish a connection" between MS-SQL and Oracle servers
This concept is known as "linked server" in MS-SQL lingo. See this MSDN article for an overview and details about sp_addlinkedserver Stored Procedure. This latter document provides the connection parameters required for various sources, including Oracle or ODBC (i.e. for Oracle you can either use ODBC, which is generally easier but less efficient, and for Oracle versions 8 and up, an OLE DB driver, which as implied may be harder to confure, but provide better performance).
Again, even with the gain associated with the Oracle OLE DB driver, the extra hop may hinder the overall performance of your setup...
mjv
2009-10-27 12:56:08
How can I establish that connection!? I what to establish connection from sql server to oracle database link! Is that possible and how. In that case, my aplication part would have ask for odbc connection to oracle just to sql server that I already use!!
Jasmin
2009-10-27 13:04:46
I think he'd prefer some instruction/guidance instead of advice, but hey its just me.
c0mrade
2009-10-27 13:13:02
@cOmrade Right you were. My quick read of the question gave me the impression that the OP was familiar with SQL linked servers. Although the question was about the `feasibility` of the affair, he/she too wanted a `recipe`. OP now has been provide all the rope needed to... ;-)
mjv
2009-10-27 13:56:15
A:
How can I establish that connection!? I what to establish connection from sql server to oracle database link! Is that possible and how. In that case, my aplication part would have ask for odbc connection to oracle just to sql server that I already use!!
Jasmin
2009-10-27 13:01:36