ODBC 10.2 test connection success but MSAccess/Office throws ORA-12154

mikesicz

I've run into a difficult-to-solve problem. I'm troubleshooting an MSAccess to Oracle connection via Oracle ODBC driver issue.

MSAccess was giving ORA "long" datatype error (can't remember error number) when using 11.2.1 driver against a years-old, tried-n-true app that was previously well behaved (only diff is use of that app with newer Oracle ODBC driver). Web search revealed problems with 11.2 ODBC driver and MSOffice tools so decided to swith to older ODBC driver. Had 10.2 ODBC driver also installed so switched to that. Now get ORA-12154 every time I try to connect with MSAccess via Oracle ODBC. Using the ODBC Admin (32 bit) allows SUCCESSFUL connection as does tnsping & other tools that use Oracle Net. Only MSAccess (or other MSOffice tools) do not.

I've verified no other tnsnames.ora files or sqlnet.ora files available and have even gone as far as completely removing all things Oracle from the PC and reinstalling only the 10.2 client (custom mode; installed only the Oracle NET and ODBC 10.2 driver). I've turned on tracing to ADMIN level and read the trace files. trace for MSAccess call vs trace for ODBC administrator call yields nearly identical trace files till "niotns" step where the ODBC admin proceeds to connect and the MSAccess call just gives a few other coments then stops. NO apparent error messages. tnsnames IS found so don't understand why giving ORA-12154.

All tools connect (tnsping works, SQL*Plus connects, ODBC admin test sucessful, TOAD connects) EXCEPT MSAccess. Problem is I have many, many MSAccess legacy apps/tools and cannot go without the fully working ODBC connection between MSAccess and Oracle.

Help... desparate for new ideas to try since I've spent many hours searching and tried all known troubleshooting steps.

Any ideas appreciated.

Thanks,

Mike

985547

Hi Mike,

I have a similar error. I can connect to database with TNSPING, and I can also connect with SQL Developer. But I am unable to connect with Access using ODBC, I get ORA-12154 error. Am also completely stumped. So if anyone can help, I would appreciate it too.

(P.S. I have tried on a number of PCs here in the office, on one PC I have here I can connect using Access and ODBC, I can see the tables, I pick on a table, it says "linking", but it never links - it just hangs forever.)

Thanks for any pointers,

Tom

985547

..We solved our problem.
Previously I was using the system login and password. This gave the error.
I then used a specific user login and password for a specific schema and we got to our data OK.

The slightly annoying thing is that we did not get an error message - it just never linked and MS Access just hung when using the system login.

Best of luck with your problem.

Cheers,
Tom

967696

it worked for my problem although its a toad issue

look to my answer here  http://stackoverflow.com/questions/12825635/ora-12154-tnscould-not-resolve-the-connect-identifier-specified-plsql-develop/17277836#17277836

Thank you much