ODBC Oracle Connection error from MS Access

oracle ms-access odbc tns
Taryn picture Taryn · Oct 7, 2010 · Viewed 9k times · Source

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

Justin Cave picture Justin Cave · Oct 7, 2010

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.

Related questions

Programatically create ODBC connection and link tables in MS Access

We are using MS Access as a reporting front-end for Oracle. The data is accessed via an ODBC connection, and there are two linked tables in Access to Oracle that are the raw data tables. I'd like to be able …

Read More
Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include: Set up an Oracle Instant Client …

Read More
Access/jet equivalent of Oracle's decode

Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter). The problem I am facing is: I should sort (order) a resultset based basically upon a status and a date (with all records having status = 2) at …

Read More