Open Oracle DB Using Excel

[jeffmb]

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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

[COLOR=black][FONT=Verdana]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()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]
   '   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"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     Set oraDynaSet = objDatabase.DBCreateDynaset(sql, 0)
     Application.StatusBar = "Importing " & oraDynaSet.RecordCount & " Responsibility Names records"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    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<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        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[/FONT][/COLOR]

<o></o>
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. The problem seems to be unique to my particular PC.<o></o>

 

[jeffmb]

For anyone interested, I was able to get around the problem by using ADO to connect to Oracle. I was never able to figure out why I could not connect multiple times on my PC with the original code but could on everyone else's PC.