Thanks for the question, jinlan.
Asked: November 15, 2018 - 10:25 pm UTC
Last updated: November 28, 2018 - 2:34 am UTC
Version: Oracle database 10.1
Viewed 10K+ times! This question is 
You Asked
Greetings,
I received the following error while trying to connect to Oracle instantclient 10.1 in MS Access 2016:
ORA-12154: TNS:could not resolve the connect identifier specified [#12154) [Microsoft][ODBC Driver Manager] SQLSetConnectAttr failed IM006 0 [Microsoft][ODBC Driver Manager]Driver’s SQLSetConnectAttr failed (#0)
I have been working on the problem for a while by looking thru the Oracle documents, OTN, and consulting w/ internal resources, but all tries in vein. Perhaps part of my problem is the lack of full understanding of a Big picture between Oracle instantclient, ODBC and system variables.
Hope the following background helps in troubleshooting my problems:
Successfully installed 64-bit Oracle instantclient to be used in 64-bit “Toad for Oracle” on my Window 7, and I have been using “Toad for Oracle” since 2014.
Need to install 32-bit Oracle instantclient to be used in 32-bit MS Access 2016 thru ODBC by using the following steps:
1. Downloaded the following Instant Client packages to my Window 7:
Version 10.1.0.5
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
instantclient-basic-win32-10.1.0.5-20060419.zip (31,926,105 bytes)
Instant Client Package - ODBC: Additional libraries for enabling ODBC applications
instantclient-odbc-win32-10.1.0.5-20060419.zip (666,571 bytes)
2. Copied both zip files to the following folders:
C:\Oracle\INS_ODBC\instantclient-basic-win32-10.1.0.5-20060419.zip
C:\Oracle\INS_ODBC\instantclient-odbc-win32-10.1.0.5-20060419.zip
3. Unzip the packages for Oracle Instantclient 10.1 and ODBC into a single directory such as instantclient_10_1:
C:\Oracle\INS_ODBC\instantclient-basic-win32-10.1.0.5-20060419\instantclient10_1
C:\Oracle\INS_ODBC\instantclient-odbc-win32-10.1.0.5-20060419\instantclient10_1
4. Copied both unzipped files from Step #3 to C:\Oracle_ODBC
5. Logged on as system administrator to add “C:\Oracle_ODBC;” to the beginning of PATH in the Environment Variables.
6. Installed instantclient ODBC by running the following file:
C:\Oracle_ODBC\odbc_install.exe
Last, but not least, is the folder C:\Oracle\Network\Admin' for the following tnsnames.ora file is defined in the TNS_ADMIN variable name and the same dB server is being used by 64-bit Oracle Instantclient:
MRS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mrs.dot.state.ak.us)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MRS.dot.state.ak.us)
)
)
and Connor said...
Everything you have done so far sounds right.
Have you setup an appropriate ODBC Data Source ? (Control Panel => Administrative => ODBC 32bit)
In terms of big picture, the premise is:
- You create an ODBC DSN called (say) "ODBC1"
- That DSN will be bound to a particular ODBC driver (Oracle 32bit)
- It will also refer to a nominated TNS entry (MRS)
- The MRS will use the instant client to connect to Oracle.
So the path is:
Access => ODBC1 => MRS tnsnames entry => sqlnet => host mrs.dot.state.ak.us, service MRS.dot.state.ak.us
The ORA-12154 means you said:
"I want to talk a something XXX, can I cannot locate anything in tnsnames.ora called XXX"
So the causes could be:
- tnsnames.ora cannot be found, or the wrong one is being used
- tnsnames.ora can be found, but no reference to anything called XXX
So
1) Go to the Drivers tab under ODBC admin - make sure the Oracle one is there (confirms correct installation)
2) make sure TNS_ADMIN is set machine wide (not just for you), because the ODBC driver needs to be aware of it
3) make sure the ODBC DSN you create is using the 32bit oracle driver and has MRS as the Oracle service name
Hope this helps
Is this answer out of date? If it is, please let us know via a Comment