What you need to use is the product called Database Gateway for ODBC (DG4ODBC) which allows you to define a database link in Oracle that uses a foreign ODBC driver to connect to a foreign data source.
So far so good - now to the details:
Edited by: kgronau on Jan 30, 2013 3:55 PM
As mentioned, DG4ODBC requires a foreign, 3rd party ODBC driver. There's one free ODBC driver for Linux, but this driver does not fulfill DG4ODBC's minimum requirements, so I would suggest to set up DG4ODBC on Windows and to use the MS Access ODBC Driver.
Even when your source Oracle database resides on a Unix server this setup will fit your needs as you can connect using SQL*Net from the Oracle database to the gateway listener - so choose DG4ODBC on Windows
Now you need to pay attention if the machine you want to use to serve the gateway is a 64bit or 32bit Windows. Depending on the word size you have to install the correct DG4ODBC product (32/64bit word size) and you also have to make sure to get the correct word size for your MS Access ODBC driver from Microsoft.
The final part is the set up. There's a note on http://support.oracle.com that describes the set up:
How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to C [Document 1266572.1]
How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Orac [Document 466225.1]
And the real tricky part now is the Gateway listener you have to configure...
Edited by: kgronau on Jan 30, 2013 4:01 PM
As your source MDB file is located on a network share, you have to make sure the the Oracle gateway listener is running in a MS Windows user account that is able to access the share. In addition when setting up the ODBC DSN, do NOT use driver letters, instead use UNC naming (\\server\share\filename).
Background info: By default, the Oracle listener is running in the local system account and this account can't access any shares. So the first step is that you have to move the Gateway Listener Service from the local system account into a user account that can access the share.
The second restriction is that drive letter assignments for network shares are only availble when a user is logged in - when you use a dedicated machine that serves the gateway you commonly do not log into this machine, so you have to use UNC naming. UNC naming uses \\<hostname>\<share>\<msaccess>.mdb as syntax and this can also be used when no user is logged in.
Well the requirement has changed. They are loading data from 4 MS access DBs into a SQL Server DB.
Now its needed to access this sqlserver DB, say SQ1, from an oracle DB, say OR1. Both are on client network, on different systems. Oracle on AIX, SQL Server on Windows.
Can you please tell me how to configure OR1 (Oracle 11gR2) on AIX to connect to SQ1 via DB link ?
Both of them are at different machines, and there is no oracle installation on SQ1 machine at present.
It is almost the same.
Microsoft does not offer (yet) a SQL Server ODBC Driver for AIX so you need to get from a 3rd party vendor a suitable ODBC Driver when you want to use DG4ODBC on AIX (see Oracle note How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Or [Document 561033.1] available through the Support portal). A couple of times I've heard that foreign 3rd party ODBC driver licenses are more expensive then purchasing Oracle's Database Gateway for ODBC which comes with everything you need to connect to a SQl Server (How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, [Document 562509.1] ) . So when you want to use DG4ODBC on the AIX box and have to purchase a license for the 3rd party ODBC driver also check out the pricing for DG4MSQL.
If you have access to the SQL Server then there's another option. You could just install the gateway DG4ODBC on this SQL Server machine and you can then use the MS SQl Server ODBC driver for Windows. The configuration is almost similar to what I mentioned above - except of the requirement to define a UNC network share to the MDB files and to move the listener from the local system account into a normal user account.
How to Configure DG4MSQL (Database Gateway for MS SQL Server) on a 64bit Windows post install [Document 1086365.1]
How to Configure DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install [Document 466267.1]