ODBC Oracle Connection error from MS Access

Question

Hi Everyone-

I currently support an MS Access database that has linked connections using the Microsoft ODBC Driver for Oracle. I can connect to the current linked tables without any issues, but we are required for security reasons to change the password on the account accessing the Server.

I have changed the password successfully but now when I attempt to relink the tables I get the error:

ODBC--call failed
Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

These tables are part of a critical application and now we can't connect. Any suggestions on how to resolve this?

Answer 1

The ORA-12154 error indicates that you are specifying a TNS alias that is not resolving. Are you using a DSN for your connection to Oracle? If so, what is the TNS alias you're specifying there? Does that alias exist in your tnsnames.ora file? Are there multiple tnsnames.ora files on the client?

The tnsnames.ora file is on the client machine where Access is installed. Without knowing what version of the Oracle client is installed, the simplest way to find out which tnsnames.ora file is being used is probably to open up a DOS prompt and type "tnsping service_name" where `service_name" is whatever TNS alias is specified in your DSN. You'll get something like

C:\Users\jcave>tnsping fuddy_duddy

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-OCT-2
010 08:10:51

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

Used parameter files:
C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora

TNS-03505: Failed to resolve name

The "Used parameter files" line tells you the directory where the TNS related files are stored. There should be a tnsnames.ora and a sqlnet.ora file in that directory.