8 Replies Latest reply on Jul 16, 2008 1:51 AM by 643871

    Oracle and VBA connection String

    643871
      i have this code to connetion my VBA script to my database. but i got the error 424 which is that error "424 object required"


      Private Sub RSWVBAPage_afterPlay()

      Dim strConnection, conn, rs
      Dim strSQL As String
      Set strConnection = New Connection
      Set conn = New ADODB.Connection
      Set rs = New recordset

      strConnection = "Provider=OraOLEDB.oracle;Data Source=192.168.1.234:1521:xe;User Id=viola-0.5.0;Password=123456;"
      'strConnection = "Driver=(OraOLEDB.oracle);dbq=192.168.1.234:8080/XE;Uid=viola-0.5.0;Pwd=123456;"

      Set conn.strConnection = Server.CreateObject("ADODB.Connection")
      conn.open strConnection

      Set rs = Server.CreateObject("ADODB.recordset")
      strSQL = "SELECT * FROM AIRLINES"
      rs.open strSQL, conn, 3, 3

      rs.MoveFirst
      While Not rs.EOF
      RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME")
      rs.MoveNext
      Wend
      rs.Close
      conn.Close
      Set conn = Nothing
      Set rs = Nothing
      End Sub

      null
        • 1. Re: Oracle and VBA connection String
          646304
          Did you reference the "Microsoft Active-X data object" library in tools/References?
          • 2. Re: Oracle and VBA connection String
            643871
            yes. Microsoft ActiveX 2.5.
            • 3. Re: Oracle and VBA connection String
              644275
              I made a few changes to your VBA Code. You still need your reference to MS ActiveX Data Object 2.5 Library. Post a reply if this works. If there are errors, reply with the line that is throwing the error.

              Dim strConnection As String
              Dim conn As ADODB.Connection
              Dim rs As ADODB.recordset
              Dim strSQL As String

              Set conn = New ADODB.Connection
              Set rs = New ADODB.recordset

              strConnection = "Provider=OraOLEDB.oracle;Data Source=192.168.1.234:1521:xe;User Id=viola-0.5.0;Password=123456;"

              conn.ConnectionString = strConnection
              conn.open

              strSQL = "SELECT * FROM AIRLINES"
              rs.open strSQL, conn, 3, 3

              rs.MoveFirst
              While Not rs.EOF
              RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME")
              rs.MoveNext
              Wend
              rs.Close
              conn.Close
              Set conn = Nothing
              Set rs = Nothing
              • 4. Re: Oracle and VBA connection String
                643871
                I made a few changes to your VBA Code. You still
                need your reference to MS ActiveX Data Object 2.5
                Library. Post a reply if this works. If there are
                errors, reply with the line that is throwing the
                error.

                Dim strConnection As String
                Dim conn As ADODB.Connection
                Dim rs As ADODB.recordset
                Dim strSQL As String

                Set conn = New ADODB.Connection
                Set rs = New ADODB.recordset

                strConnection = "Provider=OraOLEDB.oracle;Data
                Source=192.168.1.234:1521:xe;User
                Id=viola-0.5.0;Password=123456;"

                conn.ConnectionString = strConnection
                conn.open

                strSQL = "SELECT * FROM AIRLINES"
                rs.open strSQL, conn, 3, 3

                rs.MoveFirst
                While Not rs.EOF
                RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME")
                rs.MoveNext
                Wend
                rs.Close
                conn.Close
                Set conn = Nothing
                Set rs = Nothing
                still get error in bold line and this is the message appear :
                ORA-12545:Connect failed because target host or object does not exist
                still used MS. ActiveX 2.5 too and used driver MS ODBC for Oracle too
                • 5. Re: Oracle and VBA connection String
                  644275
                  I have attached a couple of links to websites that describe this error code(just googled them)

                  http://ora-12545.ora-code.com/

                  http://www.dbmotive.com/oracle_error_codes.php?errcode=12545&type=ORA

                  Hope one of them provides the answer. It sounds like the address or parameters for the connection string could be wrong in your case. Also, pinging the Oracle server and making sure it is active will ensure that you have the right address.
                  • 6. Re: Oracle and VBA connection String
                    643871
                    thanks for response.
                    thats reallyhelp me.
                    • 7. Re: Oracle and VBA connection String
                      644275
                      Were you able to connect to the database and execute that SQL query?
                      • 8. Re: Oracle and VBA connection String
                        643871
                        i make some different code like in this forum:
                        http://vbaexpress.com/forum/showthread.php?t=18516

                        and so far i can connect oracle database with vba. :)
                        coz after i change name of listener i had still get error.