5 Replies Latest reply: Aug 5, 2014 6:05 AM by Kgronau-Oracle RSS

    MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154

    Gumas

      Dear All,

       

      I'm trying to use MS Access .mdb file as source to my OWB application. I need to connect to the ms access .mdb file first using odbc. Here are the step I do:

       

      1. Define ODBC connectifity

           control panel -> admin tools -> data sources -> System DSN -> Add

           Choose Microsoft Access Driver (*.mdb) -> Finish

           Data source name: msaccess -> Database: Select -> .mdb file location (C:\MSAccess\att2000..mdb)

       

      2. Configure Listener

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

            (PROGRAM = extproc)

            (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

          )

          (SID_DESC =

            (SID_NAME = msaccess

            (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

            (PROGRAM = hsodbc)

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          )

          (SID_DESC =

            (SID_NAME = msaccess

            (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

            (PROGRAM = hsodbc)

          )

        )

       

      ADR_BASE_LISTENER = C:\app\Administrator

       

      3. Configure Oracle HS

           File name initmsaccess.ora

       

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO = msaccess

      HS_FDS_TRACE_LEVEL = 1

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set <envvar>=<value>

       

      4. Edit tnsnames.ora

      msaccess=

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

          (CONNECT_DATA =

            (SID=msaccess)

            (SERVICE_NAME=msaccess)

          )

        (HS=OK)

        )

       

      5. create database link

      create database link msaccess using 'msaccess';

       

      6. Run a query to test connection

       

      SQL> select * from departments@msaccess;

       

      ERROR at line 1:

      ORA-12154: TNS:could not resolve the connect identifier specified

       

      I check the tns using tnsping msaccess, it return

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos

      t)(PORT = 1521)) (CONNECT_DATA = (SID=msaccess) (SERVICE_NAME=msaccess)) (HS=OK))

      OK (120 msec)

       

      Any body know how to solve this?

       

      Best Regards

      Akhmad H Gumas

        • 1. Re: MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154
          Kgronau-Oracle

          There are some issues.

           

          1. The executable in your Listener: In 11.2 the program is called dg4odbc - not hsodbc. So please make sure to change the line (PROGRAM = hsodbc) to (PROGRAM = dg4odbc) - then STOP and START the listener agian.

          2. The 12154 error means that the alias you specified in the create database link statement isn't found in your tnsnames.ora file (of the database). Did you use the tnsping utility of the Oracle database home to check the connectivity and is the masccess alias stored in the tnsnames.ora file used by your Oracle database?

            

          In addition there's an issue with the tns alias.

          You have in your tnsnames.ora   (SID=msaccess) and  (SERVICE_NAME=msaccess) - please use SID OR Service_Name - but not both.

           

          - Klaus

          • 2. Re: MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154
            Gumas

            Thanks Klaus for your reply, really appreciate.

             

            Here are my listener.ora and tnsnames.ora after updated to dg4odbc and only use SID.


            tnsnames.ora

            msaccess=

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                (CONNECT_DATA =

                  (SID=msaccess)

                )

              (HS=OK)

              )

             

            listener.ora

            # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

            # Generated by Oracle configuration tools.

             

             

            SID_LIST_LISTENER =

              (SID_LIST =

                (SID_DESC =

                  (SID_NAME = CLRExtProc)

                  (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

                  (PROGRAM = extproc)

                  (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

                )

                (SID_DESC =

                  (SID_NAME = msaccess

                  (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

                  (PROGRAM = dg4odbc)

                )

              )

             

             

            LISTENER =

              (DESCRIPTION_LIST =

                (DESCRIPTION =

                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                )

                (SID_DESC =

                  (SID_NAME = msaccess

                  (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

                  (PROGRAM = dg4odbc)

                )

              )

             

             

            ADR_BASE_LISTENER = C:\app\Administrator

             

            after i run the query again, the error message change to:

             

            ERROR at line 1:

            ORA-28545: error diagnosed by Net8 when connecting to an agent

            Unable to retrieve text of NETWORK/NCR message 65535

            ORA-02063: preceding 2 lines from MSACCESS

             

            The listener is in my local machine. I use the tns also in my local machine. and the database I use to run the query is oracle db on my local using SH user.

             

            do you have any idea about this?

             

            Best Regards

            Akhmad H Gumas

            • 3. Re: MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154
              Kgronau-Oracle

              Akhmad,

              just to confirm I got it right:

              You have an Oracle database and the gateway installed on your machine. The database and the gateway are all part of this Oracle_Home:  C:\app\Administrator\product\11.2.0\dbhome_1

              Correct?

              When looking at your listener.ora file there is a closing bracket missing:

              (SID_NAME = msaccess

              => should be (SID_NAME = msaccess)

               

              Once done, please STOP and START the listener again.

               

              Then open now a command line window and type:

              set ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1

              C:\app\Administrator\product\11.2.0\dbhome_1\bin\lsnrctl status

              C:\app\Administrator\product\11.2.0\dbhome_1\bin\dg4odbc

              C:\app\Administrator\product\11.2.0\dbhome_1\bin\tnsping msaccess

              And copy/paste the output of each command.

               

              - Klaus

              • 4. Re: MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154
                Gumas

                Klaus,

                 

                just fix the point you mention.

                here are the execution result:

                 

                C:\>C:\app\Administrator\product\11.2.0\dbhome_1\bin\lsnrctl status

                 

                LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-AUG-2014 17:10

                :23

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

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

                STATUS of the LISTENER

                ------------------------

                Alias                     LISTENER

                Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

                ction

                Start Date                05-AUG-2014 17:09:18

                Uptime                    0 days 0 hr. 1 min. 5 sec

                Trace Level               off

                Security                  ON: Local OS Authentication

                SNMP                      OFF

                Listener Parameter File   C:\app\Administrator\product\11.2.0\dbhome_1\network\a

                dmin\listener.ora

                Listener Log File         c:\app\administrator\diag\tnslsnr\eric-fd165f15bb\list

                ener\alert\log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

                Services Summary...

                Service "CLRExtProc" has 1 instance(s).

                  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                Service "msaccess" has 1 instance(s).

                  Instance "msaccess", status UNKNOWN, has 1 handler(s) for this service...

                The command completed successfully

                 

                C:\>C:\app\Administrator\product\11.2.0\dbhome_1\bin\dg4odbc

                Oracle Corporation --- TUESDAY   AUG 05 2014 17:12:18.509

                Heterogeneous Agent Release 11.2.0.1.0 - Production  Built with

                   Oracle Database Gateway for ODBC

                 

                C:\>C:\app\Administrator\product\11.2.0\dbhome_1\bin\tnsping msaccess

                TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-AUG-2

                014 17:12:48

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

                Used parameter files:

                C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

                Used TNSNAMES adapter to resolve the alias

                Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos

                t)(PORT = 1521)) (CONNECT_DATA = (SID=msaccess)) (HS=OK))

                OK (20 msec)

                 

                Looks like the problem is the missing closing bracket. Finally the query work after i fix it. Thanks a lot Klaus

                 

                Best Regards

                Akhmad H Gumas

                • 5. Re: MS Access .mdb connection using ODBC could not resolve the connect identifier ORA-12154
                  Kgronau-Oracle

                  Well done Akhmad!

                   

                  Let me comment a few things:

                  1.

                  Service "msaccess" has 1 instance(s).

                    Instance "msaccess", status UNKNOWN, has 1 handler(s) for this service...

                  => that's correct.The gatewa yis spawned by the listener on the fly, hence the status will always show up as "unknown"

                   

                  2. Version 11.2.0.1.0 => I would recommend you to use the latest release (11.2.0.4). You can download it from "My Oracle Support" portal (as long as you have a valid support contract). Click on Patches & Updates and look for "Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER". Make sure to cho0se the correct platform and then download the first and second CD.

                   

                  - Klaus

                   

                  P.S.: Please do not forget to mark the thread as answered.