I have an Oracle Server 126.96.36.199.0 Enterprise Edition installed on a 64-bit Windows Server 2008 R2 Standard. I need to be able to connect from it to MS-Access and have an Oracle stored procedure run DML commands on tables in an Access database.
Following some advice I found on the Net, I did the following steps, all on the Oracle server itself:
I created a new System DSN ODBC called PristineDSN with the "Microsoft Access Driver", using the abovementioned MS-Access database. Note that I did not use the one under Start->"Administrative Tools"->"Data Sources (ODBC)" because it did not have the "Microsoft Access Driver". I ran odbcad32.exe under C:\Windows\SysWOW64 instead.
I created a file called initPristineDSN.ora under $ORACLE_HOME\hs\admin containing the following three lines:
HS_FDS_CONNECT_INFO = PristineDSN
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS = FALSE
I opened the file listener.ora under $ORACLE_HOME\NETWORK\ADMIN
and added the following four lines to the SID_LIST_LISTENER entry:
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
I opened the file tnsnames.ora under $ORACLE_HOME\NETWORK\ADMIN
and appended the following six lines:
I ran the following two commands from a command prompt in order to stop and then start the listener:
At this point I ran TNSPING PRISTINEDSN from a Command Prompt window, and the result was:
TNS Ping Utility for 64-bit Windows: Version 188.8.131.52.0 - Production on 05-DEC-2011 13:46:47
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (CONNECT_DATA=(SID=PristineDSN)) (HS=OK))
OK (20 msec)
I connected to a schema and ran the folowing command to create a database link:
CREATE DATABASE LINK DBL_PRISTINE USING'PRISTINEDSN';
Now, I ran the following command:
SELECT * FROM TBL_PRODUCTS@DBL_PRISTINE;
I received the following output:
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from DBL_PRISTINE
The procedure above worked on a previous machine I had, running Oracle Server 10.2.0.1.0 Enterprise Edition on a 32-bit Windows Server 2003 SP2. What am I doing wrong?
Perhaps there is a simpler way to connect from Oracle 11g to MS-Access?
: All instances of $ORACLE_HOME in the steps above have been substituted for E:\ORACLE\product\11.2.0\d
b which is the value of ORACLE_HOME under the HKEY_LOCAL_MACHINE\SOFTWAR
g_home1 key in the server's registry.
Also, Microsoft Access is not installed on the server; only the Microsoft Access Driver is.