VBA ADO Failed to Connect to Oracle

[goss]

Hi all,

Using Excel 2007.

I am trying to connect and read records from 2 Oracle tables

I am receiving this error message,

Run-time error '-2147467259 (80004005)':
ORA-12545: Connect failed because target host or object does not exist

Debug points here

  .Open (strConn) 'Open the connection.

Any idea what I'm doing wrong?
Full code below

thx
w

Option Explicit

Sub GetOracleData()

    'Comments
    'References:
    '====================================
    '1.) Micorsoft ActiveX Data Objects 2.1
    
    'Declarations
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strConn As String
    Dim conn As ADODB.Connection
    Dim rst1, rst2 As ADODB.Recordset
    Dim strSQL As String
    Dim strOraServer As String
    Dim strOraUID As String
    Dim strOraPWD As String
    Dim strOraTbl1 As String
    Dim strOraTbl2 As String
    Dim stSQL1 As String
    Dim stSQL2 As String
        
    'Initialize
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Dash")
    Set conn = New ADODB.Connection
    Set rst1 = New ADODB.Recordset
    Set rst2 = New ADODB.Recordset

    With ws
        strOraServer = .Range("B8")
        strOraUID = .Range("B9")
        strOraPWD = .Range("B10")
        strOraTbl1 = .Range("B11")
        strOraTbl2 = .Range("B12")
    End With
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Temprary worksheets to handle data
    Dim wbtmp As Workbook
    Dim wsTbl1 As Worksheet
    Dim wsTbl2 As Worksheet
     
    Set wbtmp = Workbooks.Add
    With wbtmp
        .SaveAs Filename:="C:\tmp\OracleDataTest.xlsx"
        .Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "tbl1"
        .Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "tbl2"
        Set wsTbl1 = .Worksheets("tbl1")
        Set wsTbl2 = .Worksheets("tbl2")
    End With
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
    'Oracle Connection String
    'strOraServer includes IP Address:Port:SID
        strConn = _
        "Provider=OraOLEDB.oracle;" & _
        "Data Source=" & strOraServer & ";" & _
        "User Id=" & strOraUID & ";" & _
        "Password=" & strOraPWD & ";"
    
    'Get data from Oracle Tables
    
    'Table1
    stSQL1 = ""
    stSQL1 = "SELECT * FROM " & strOraTbl1
     
    'Table2
    stSQL2 = ""
    stSQL2 = "SELECT * FROM " & strOraTbl2
     
     'Clear the destination worksheets
     wsTbl1.UsedRange.ClearContents
     wsTbl2.UsedRange.ClearContents
     
    With conn
        .Open (strConn) 'Open the connection.
        .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    End With
     
    With rst1
        .Open stSQL1, conn 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With
     
    With rst2
        .Open stSQL2, conn 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With
     
    'Copy the recordsets
        wsTbl1.Cells(1, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
        wsTbl2.Cells(1, 1).CopyFromRecordset rst2 'Copy the 2nd recordset.
        
    'Tidy up
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing
    conn.Close
    Set conn = Nothing
    
    Set wb = Nothing
    Set ws = Nothing
    Set wbtmp = Nothing
    Set wsTbl1 = Nothing
    Set wsTbl2 = Nothing
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

 

[Firefly2012]

Hi

Nothing wrong with your connection string structure, so I presume it must be the data source variable causing you problems (ie it isn't correct). So check what you have in Dash!B8

 

[goss]

Thanks Firefly,

I double-checked against my tnsnames.ora file and with the administrator.
It seems to check out.

The value in $B$8 is host:1521:SID
There are no leading or trailing spaces.

thx
w

 

[Firefly2012]

Does the code work from any computer at all? Searching Google on that error doesn't turn up anything concrete other than it seems hard to debug. The following link may prove useful:

http://www.dba-oracle.com/t_ora_124...ause_target_host_or_object_does_not_exist.htm

 

[goss]

Thanks Firefly,

I'm researching the link you sent, and we are testing the code on another machine.
We can link to the tables through MSAccess Link Table Manager if that is of any use.

My end in mind is to remove the linked tables.

thx
w