4 Replies Latest reply: May 17, 2010 10:50 AM by oBean RSS

    OO4O Different Results Same Server

    oBean
      I am trying to connect to Oracle 10gR2 from Excel VBA (OracleInProcServer 5) and Oracle Client 10.2.0. The same code used to work on my old PC, but I've got a new one and a new Oracle client install and now I get errors. To complicate things our Oracle databases have recently moved as well.

      there are 3 databases, on 2 servers...here are the results for each given this code:

      Sub TestOracle()

      Set objSession = CreateObject("OracleInProcServer.XOraSession")
      Set objDataBase = objSession.OpenDatabase("TEST", "username/password", 0&)

      End Sub

      Server 1
      TEST I get ORA-12545
      DVL I get a successful connection

      Server 2
      PRD I get ORA-01034

      I can connect using SQL Plus without any problems.

      What's odd to me are the different errors...I'm not sure how to troubleshoot this...

      tia
        • 1. Re: OO4O Different Results Same Server
          oBean
          What about NOT using OO4O?

          Just wondering, is it possible to connect to Oracle using the Instant Client from Excel VBA?

          I know it's easily done with .NET by simply adding a reference to System.Data.OracleClient, placing the few Instant Client DLL's in the executables folder, and setting PATH and TNS_ADMIn vars...but Excel I don't see a way...

          ???
          • 2. Re: OO4O Different Results Same Server
            oBean
            Anyone?
            • 3. Re: OO4O Different Results Same Server
              oBean
              Taking another look at this problem...

              It looks like it is not specific to OO4O. If I use an ADO connection, with a DSN, I get the same exact results. The DSN is using the Oracle client driver, and connects successfully using the "test" feature. Also, tnsping is successful for all 3 connections on this PC, as well, sqlplus connects just fine. It's just Excel...

              Server 1
              TEST I get ORA-12545
              DVL I get a successful connection

              Server 2
              PRD I get ORA-01034

              Very odd, I just can't figure why this one PC would have 3 different issues!
              • 4. Re: OO4O Different Results Same Server
                oBean
                I just realized my ADO connection isn't using a DSN after all...somethings twisted on this PC, just can't figure what. here's the ADO code:

                Sub TestConnection()

                Dim con As New ADODB.Connection
                con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DVL;User ID=xxx;Password=xxx;"
                'con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=TST;User ID=xxx;Password=xxx;"
                'con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=PROD;User ID=xxx;Password=xxx;"
                con.Open

                Dim objCmd As New ADODB.Command

                objCmd.CommandType = adCmdText
                objCmd.CommandText = "select sysdate from dual"
                objCmd.ActiveConnection = con

                Dim objRes As New ADODB.Recordset

                Set objRes = objCmd.Execute

                objRes.MoveFirst

                MsgBox objRes.Fields(0).Value

                con.Close

                End Sub