Error 440 on Oracle Database Recreate

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 seems to be correct and is used by other Oracle applications.

Sub Resp_Name()

Dim objSession As Object

Dim objDatabase As Object

Dim oraDynaSet As Object

Dim x As Long

Dim y As Long

Dim ws As Worksheet

Dim sql As String

   '   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/mypassword", 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", "myname/mypassword", 0).  I have to close Excel and reopen the spreadsheet to get this to run one more time.

Answer 1

Hi JP

Thanks for the quick response. I had already tried oraDynaSet.Close and objDatabase.Close.

objSession .Close is not a valid command, nor is oraDynaSet.Release. As I stated above, the error occurs at the following line:

   Set objDatabase = objSession.OpenDatabase("COMPRD", "myname/mypassword", 0)

Answer 2

Hi Jeff,

As we cannot test it is difficult to provide THE answer.

What I see and suggest. Close the everything before recalling it.

oraDynaSet.close

objSession .close

objDatabase.close

maybe relaease also the DynaSet?

Next, run the code in step mode (F8) and check were the error occurs.

As good practice, it is advised to put the errorhandling at the end:

   ....

    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

Can be written as:

....

    End If

    Set objSession = Nothing

    Set objDatabase = Nothing

    Exit Sub

my_Error9:    ' Error Trap

    MsgBox "Error " & Err & " - " & Error(Err), , "Oracle Database Error" ' Display error number & description

End Sub

HTH

Wkr,

JP Ronse