I thought I had this particular subject down cold, but the last few days have had me running in circles and doubting my sanity.
On Windows clients, given
1) the system variable TNS_ADMIN set to a directory on a remote file server
2) One or two local ORACLE_HOMEs for oracle client. Either 32-bit or 64-bit or one of each. Each with its own copy of tnsnames.
3) The local copies of tnsnames.ora has incorrect data regarding one database (database host name has changed).
We have observed several cases of the local app (app is running on subject desktop) fails to connect. This particular app replaces the actual error with a generic 'database connection failed." Given that the database in question was moved to a new server last week, we suspected 'listener does not know of service" This error would be expected if they were still using their local tnsnames, but we have set TNS_ADMIN to point to the location of a good file. tnsping indicates it is finding sqlnet.ora at the TNS_ADMIN location. sqlplus connects just fine. But the app doesn't. So I correct the HOST name in the local tnsnames, and the app is able to connect. Then as a further test, we "delete" (rename) the local tnsnames.ora and restart the app. So, at this point the only way the net service name can be resolved is via the tnsnames.ora at TNS_ADMIN. And it does. But only if there is no local file.
So the behavior is as if there was a search order, looking first in %ORACLE_HOME%\network\admin and if no tnsnames found there, then and only then looking in %TNS_ADMIN%.
That's crazy enough, but it gets worse. In one case, even after correcting %ORACLE_HOME%\network\admin\tnsnames.ora, the app still failed. Searching for other copies of tnsnames.ora, we found on one the Windows desktop! (c:\users\<username>\Desktop\tnsnames.ora). After correcting that copy, the app was able to connect.
FWIW, this app connects via ODBC.
And just to add another wrinkle - in playing around with this on my own workstation, I get just the opposite, and see it with just tnsping. If TNS_ADMIN points to a good location with a good tnsnames, that one is used. But if tns_admin points to an invalid location, or a valid location but no tnsnames.ora, it will roll over to %ORACLE_HOME%\network\admin.