none
Open Oracle DB Using Excel RRS feed

  • Question

  • I am using the following subroutine to open and read an Oracle database table. It had worked fine for over a year until my PC crashed and I had to use a new PC. Now it works only the first time through and gives me the error message, "Error 440 - Unable to make connection, ORA-12154: TNS:could not resolve service name". The subroutine works fine on all the other PCs that I tested. The TNSnames.ORA file is correct and is used by other Oracle applications.

     

    Option Explicit
    Dim objSession As Object
    Dim objDatabase As Object
    Dim oraDynaSet As Object
    Dim x As Long
    Dim y As Long
    Dim i As Long
    Dim sql As String
    Dim ws As Worksheet
    Sub Resp_Name()


       '   Does Resp_Name sheet exist?
        On Error Resume Next
        Set ws = Worksheets("Resp_Name")
        On Error GoTo 0
        If Not ws Is Nothing Then
        Sheets("Resp_Name").Select
        Cells.Select
        Selection.ClearContents
        Else
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Resp_Name"
        End If
       
        On Error GoTo my_Error9
        Set objSession = CreateObject("OracleInProcServer.XOraSession")
        Set objDatabase = objSession.OpenDatabase("COMPRD", "myname/password", 0)
        On Error GoTo 0
       
        sql = "select " _
        & " responsibility_name" _
        & ",menu_id " _
        & "from" _
        & " fnd_responsibility_vl " _
        & "where" _
        & "     end_date is null " _
        & " or end_date > sysdate " _
        & "order by" _
        & " responsibility_name"

         Set oraDynaSet = objDatabase.DBCreateDynaset(sql, 0)
         Application.StatusBar = "Importing " & oraDynaSet.RecordCount & " Responsibility Names records"

        If oraDynaSet.RecordCount > 0 Then
            oraDynaSet.MoveFirst    ' Get field description fields
            For x = 0 To oraDynaSet.Fields.Count - 1
                Sheets("Resp_Name").Cells(1, x + 1) = oraDynaSet.Fields(x).Name
                Sheets("Resp_Name").Cells(1, x + 1).Font.Bold = True
            Next

            For y = 0 To oraDynaSet.RecordCount - 1         ' Get detail records
                For x = 0 To oraDynaSet.Fields.Count - 1    ' Get record fields
                    Cells(y + 2, x + 1) = oraDynaSet.Fields(x).Value
                Next
                oraDynaSet.MoveNext
            Next
            Sheets("Resp_Name").Range("A2").Select
        End If
       
        GoTo my_continue9
    my_Error9:    ' Error Trap
        MsgBox "Error " & Err & " - " & Error(Err), , "Oracle Database Error" ' Display error number & description
       
    my_continue9:
        Set objSession = Nothing
        Set objDatabase = Nothing
    End Sub

    The second time through the error occurs on the line, Set objDatabase = objSession.OpenDatabase("COMPRD", "smith" & "/" & "password", 0).  I have to close Excel and reopen the spreadsheet to get this to run one more time.

     

     

     

    Wednesday, December 21, 2011 8:59 PM

Answers

  • Hi Jeffmb,

     

    I think your error is more related to Oracle then to Excel. I suppose you could check the Oracle Support or search on the error message you receive and to see if that resolves your problem.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 11, 2012 9:45 PM
    Moderator
  • While I was never able to figure out why my code stopped working, I was able to get around the problem by connecting to Oracle via ADO
    Tuesday, February 14, 2012 9:29 PM

All replies

  • Hi Jeffmb,

     

    I think your error is more related to Oracle then to Excel. I suppose you could check the Oracle Support or search on the error message you receive and to see if that resolves your problem.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 11, 2012 9:45 PM
    Moderator
  • While I was never able to figure out why my code stopped working, I was able to get around the problem by connecting to Oracle via ADO
    Tuesday, February 14, 2012 9:29 PM
  • Easer to use ODBC driver:

    Const ODBC$ = "Provider=MSDASQL.1;Persist Security Info=True;User ID=OraAccountName;Data Source=Name_ODBC;Password=OraPass"
    Dim c As New ADODB.Connection
    Dim d As New ADODB.Recordset
    Dim pytanie$
    
    Sub pobieramy_dane_z_Ora()
    'MVP OShon from VBATools.pl
    Dim ilosc_rek&, y&
            
    On Error GoTo brak_pol
            If c.state <> 1 Then c.Open ODBC
            If D.state = 1 Then D.Close
            D.Open Pytanie, c, adOpenStatic, adLockReadOnly
    On Error GoTo 0
    If D.RecordCount = 0 Then GoTo brak_danych
    
    y = 2
    For q = 1 To D.Fields.Count
        Cells(1, q) = D.Fields.item(q - 1).Name
    Next q
    
    While Not D.EOF
        For z = 1 To D.Fields.Count
            Cells(y, z).value = D.Fields(z - 1)
        Next z
        y = y + 1
        D.MoveNext
    Wend
    Koniec:
       D.Close
    Exit Sub
    brak_danych:
        MsgBox "No data", vbCritical, "Error"
    Resume koniec
    Exit Sub
    brak_pol:
        Debug.Print Err.Number & " -" & Err.Description
    Resume koniec
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, February 15, 2012 3:09 PM
    Answerer
  • Basically, that is how I ended up coding it.
    Thursday, February 16, 2012 4:06 PM