Connecting to Oracle DB by IP

[Joe-LC]

Hi,

I am running Excel 2007 and connecting to an Oracle DB.

I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.

How do I modify the connection info below to have it connect via IP?

I tried "Data Source = 10.1.1.10\orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.

Here is what I have now:

Sub Create()
    Dim Servername As Range
    Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")

    Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
    Set EndDate = ActiveWorkbook.Sheets("Summary").Range("B5")
    Set SearchUser = ActiveWorkbook.Sheets("Summary").Range("B19")
    Range("D2:H100").Clear
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=MSDASQL.1;Password=12345;Persist Security Info=True;User ID=ABCDE;Extended Properties=""DSN=" & Servername & ";UID=" & UserName & ";" _
        , "PWD=" & Password & ";"";Initial Catalog=(Default)"), Destination:=Range("$D$2")).QueryTable
        .CommandType = xlCmdSql
        
        .CommandText = Array( _
        "SELECT ... <edited out="">", _

        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
        .ListObject.DisplayName = "Query"
        ActiveSheet.ListObjects("Query").TableStyle = "TableStyleMedium4"
        '
        ' Adds a 'Totals' row and sums the columns
        ActiveSheet.ListObjects("Query").ShowTotals = True
        ActiveSheet.ListObjects("Query").ListColumns("Burndown").TotalsCalculation = xlTotalsCalculationSum
        ActiveSheet.ListObjects("Query").ListColumns("Start").TotalsCalculation = xlTotalsCalculationSum
        ActiveSheet.ListObjects("Query").ListColumns("End").TotalsCalculation = xlTotalsCalculationSum
        ' Remove defaulted filter
        Range("Query[[#Headers],[End]]").Select
        Selection.AutoFilter
        ' Refreshes the query to align columns better
        ActiveWorkbook.RefreshAll

    End With

End Sub

Sub Refresh_Query()
'
' Button Click Macro
    ActiveWorkbook.RefreshAll
'
End Sub

Thanks a lot for taking a look!

Joe

</edited>

 

[Joe-LC]

Anyone have any suggestions? Surely there must be a way to connect via IP.. If not, what about connecting via an entry in the tnsnames.ora file?

 

[ChrisM]

You can do exactly this, I just no longer have my sample in front of me anymore to give you a perfect example sorry. Basically, go into the tnsnames file and find the full server address, ie "server.yourcompany.com port:1122"

Then in your connection string swap out the DSN entry for the full path.

from: http://www.codeproject.com/Articles/2304/ADO-Connection-Strings#ORACLE

strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
"Uid=MyUsername;Pwd=MyPassword;");

Hopefully that is enough to get you started.