I am having problems accessing an Oracle database through ODBC in Access and hope someone has some tips. I spent time trying to find a solution, but nothing useful came.
I have a connection setup in ODBC that accesses the Oracle 9 database. I can use the Test option for the ODBC administrator and receive a Test Successful message. I can also connect to the database 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 trying to connect:
ODBC--Call failed.
[Oracle] [ODBC] [Ora] ORA-12154: TNS: could not resolve service name (# 12154) [Microsoft] [ODBC Driver Manager] Driver SQLSetConnectAttr failed IM006 0 [Microsoft] [ODBC Driver Manager] Driver SQLSetConnectAttr failed (# 0)
I know that TNS searching works because the ODBC Admin tool works the same as tnsping. The question is, why does it work outside of Access, but Access cannot do this?
EDIT (2012-02-22 15:05): I just tried it on another PC, and the same thing happens, although another user logged in and the connection worked for them, so it seems to be related to my WinXP profile. Does it help?
Any advice is appreciated.
BBZ
source
share