Cannot access ODBC DB with MS Access2007

Question

I'm having problems accessing an Oracle database via ODBC in Access and hope someone has some advice. I've spent a fair time trying to find a solution, but nothing useful has come up.

I have a connection setup in ODBC that access an Oracle 9 database. I can use the Test option on ODBC administrator and receive a Test Successful message. I can also connect to the DB using SQL*PLUS. However, when I try to create a new linked table in Access 2007 and use the ODBC option, I get the following error when it tries to connect:

ODBC--Call failed.

[Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name (#12154)[Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed IM006 0 [Microsoft][ODBC Driver Manager]Driver's SQLSetConnectAttr failed (#0)

I know the TNS lookup is working because ODBC Admin tool works as does tnsping. The question is, why does it work outside Access but Access can't do it?

EDIT (2012-02-22 15:05): Just tried on a different PC and the same thing occurs, although another user logged in and the connection worked for them, so it appears to be linked to my WinXP profile. Does this help any?

Any advice would be greatly appreciated.

BBz

Answer 1

I think I've resolved the issue, but it took some digging. Using Process Monitor from Sysinternals, I discovered that Access had found a sqlnet.ora file in the "My Documents" folder of my profile and was using this in preference to the global tnsnames.ora file.

We had previously disabled sqlnet.ora (renamed the file in the Oracle folder) but Access obvisouly checks other locations for it. I've renamed the file and can now access the DB as expected via ODBC.

Interesting what you can learn!

Hope somebody finds this useful one day.

Thanks for reading BBz