How do I deal with ORA-28545 when trying to connect from Oracle to MS-Access?

I have an Oracle Server 11.2.0.2.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:

1

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.

2

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

3

I opened the file listener.ora under $ORACLE_HOME\NETWORK\ADMIN and added the following four lines to the SID_LIST_LISTENER entry:
(SID_DESC=
  (SID_NAME=PristineDSN)
  (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
  (PROGRAM=hsodbc))

4

I opened the file tnsnames.ora under $ORACLE_HOME\NETWORK\ADMIN and appended the following six lines:
PRISTINEDSN =
 (DESCRIPTION=
  (ADDRESS_LIST =
   (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (CONNECT_DATA=(SID=PristineDSN))
 (HS=OK))

5

I ran the following two commands from a command prompt in order to stop and then start the listener:
lsnrctl stop
lsnrctl start

At this point I ran TNSPING PRISTINEDSN from a Command Prompt window, and the result was:
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 05-DEC-2011 13:46:47

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
E:\ORACLE\product\11.2.0\db\network\admin\sqlnet.ora


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)

6

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?


Note: All instances of $ORACLE_HOME in the steps above have been substituted for E:\ORACLE\product\11.2.0\db which is the value of ORACLE_HOME under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1 key in the server's registry.
Also, Microsoft Access is not installed on the server; only the Microsoft Access Driver is.
netformxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
there is some version change......... check this

>>>ORACLE_HOME = D:\oracle\product\10.2.0\db_1<<<

>>E:\ORACLE\product\11.2.0\db\network\admin\sqlnet.ora<<

if it is just a typo error then check the global_name parameter in DB i.e
SQL>show parameter global_name
if it is true set it to false.
SQL>alter system set global_names=false ;

check this doc for more info

http://www.orafaq.com/forum/t/147423/2/

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206

0
netformxAuthor Commented:
praveencpk,

This is not a typo. The value of ORACLE_HOME under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1 key in the server's registry really is E:\ORACLE\product\11.2.0\db and this is also the correct path on the server's hard disk.
Also, the global_names parameter is already set to FALSE.

I have no idea what to do with the two links you provided. If you have any more suggestions, I would be happy to know.
0
OP_ZaharinCommented:
"(SID_DESC=
  (SID_NAME=PristineDSN)
  (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
  (PROGRAM=hsodbc))"

- do you have 2 oracle installation on the same machine? if not kindly confirm that you have change the above D:\oracle\product\10.2.0\db_1 to E:\ORACLE\product\11.2.0\db
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

netformxAuthor Commented:
OP_Zaharin (and also praveencpk),

Now I see what you both meant, and stand corrected. (I hold a faulty copy/paste to blame.) Allow me to rewrite the four lines I added to the SID_LIST_LISTENER entry of listener.ora in step #3 above:
(SID_DESC=
 (SID_NAME=PristineDSN)
 (ORACLE_HOME = E:\ORACLE\product\11.2.0\db)
 (PROGRAM=hsodbc))

Open in new window

Sorry for the mixup. Your turn.
0
OP_ZaharinCommented:
- did you modify your sqlnet.ora to the following?:
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

- after the changes above, restart the listener. if its still not working, i think my team have a similar issue when setting up HS recently. on Oracle 11g, there is no longer hsodbc. instead you have to setup Database gateway for ODBC - dg4odbc. the setup is almost similar except that the (PROGRAM=hsodbc) is changed to (PROGRAM=dgodbc). look further in the Oracle doc for the steps.
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OP_ZaharinCommented:
- here is the doc for 11.1. i believe the steps is the same for 11.2:
http://docs.oracle.com/cd/B28359_01/gateways.111/b31043/configodbc.htm
0
netformxAuthor Commented:
OP_Zaharin,

Using Database Gateway worked. I had to install Microsoft Access Driver for 64-bit and then spend some more time investigating why it did not work at first, but then I changed your (PROGRAM=dgodbc) into (PROGRAM=dg4odbc) and then it worked.

Thanks for your help.
0
OP_ZaharinCommented:
Hi netformx, yeah its typo. PROGRAM=dg4odbc is the correct one. glad it finally work for you.

cheers,
OP
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.