Oracle Data Link To Remote XE Instance To Access MDB

OK, I spent all day trying to make this work and it feels like such a waste. To prevent this from taking so long in the future, I thought I'd post some notes on how I was able to make this work, but first let me explain what we wanted to do.

We have an instance of Oracle Standard One database server storing the data for our main applications. We needed to connect this to an MS Access data file that lives on a completely separate system. Unlike Microsoft SQL Server, MS Access doesn't "listen" on a certain port number for requests to respond to. So what we did is install the free Oracle XE database software on the separate system. We pointed a System DSN at this MDB file, adjusted our Oracle XE to point to the DSN through the use of the "Heterogeneous Services" and could now query the Access file. Next I created a database link on our main instance of Oracle Standard One to reference the Oracle XE instance on the separate system (since it will listen for requests) and we can now query across those two systems into the MS Access file.

Here are the brief notes on how I set this up:

To setup a Database Link on your central Oracle server to connect to an external instance of Oracle XE that will talk to a MS Access database file, do the following: Configure your central Oracle server: 1.) Login to the server 2.) Edit the TNSNAMES.ORA file with an entry to point to the XE server by filling in the server name, port number and unique name (make sure to replace the ***unique name*** with your own name, minus the *** in either side.) ***unique name*** = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SID = ***unique name***) ) (HS = OK) ) 3.) Create a SYSTEM DSN a.) For username enter "username/password" b.) Set the "TNS Service Name" to the one you specified above c.) Test connection to make sure it's working 4.) Configure Heterogeneous Services a.) Open the folder %ORACLE_HOME%\HS\ADMIN b.) Copy the file "inithsodbc.ora" and name it "init***unique name***.ora" c.) Modify the line for the "HS_FDS_CONNECT_INFO" parameter to point to ***unique name*** Configure remote Oracle XE instance for MS Access database 1.) Login to the server 2.) Modify the C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora file (SID_DESC = (PROGRAM = hsodbc) (SID_NAME = ***unique name***) (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server) ) 3.) Modify the C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora file ***unique name***= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SID = ***unique name***) ) (HS=OK) ) 3.) Create a SYSTEM DSN a.) Name should be ***unique name*** b.) Select the access database .mdb file 4.) Configure Heterogeneous Services a.) Open the folder %ORACLE_HOME%\HS\ADMIN b.) Copy the file "inithsodbc.ora" and name it "init***unique name***.ora" c.) Modify the line for the "HS_FDS_CONNECT_INFO" parameter to point to ***unique name***