Provider not installed properly & at times Connection not open RRS feed

  • Question

  • Hi developers,

    I am almost very new to Macros VBA. I did much R&D to fetch data from Oracle database to excel sheet. I don't have any oracle software in my machine nor any drivers related to oracle. I request someone to provide me exact oracle  required drivers link to fetch the data. I am running Windows 7 64 bit OS & excel 2010 32 bit. 

    I am asking proper required drivers again in the question because, I installed 64 bit 10g Release 2 ODAC for Windows Itanium.

    Now, my question is, when I run the below script it throws Provider may not be installed properly or connection not open sometimes. I am confused totally on getting the data from oracle database. I don't know what is TNSNAMES.ORA file also & its location. Some body please help me, I urge, in completing this fetching task from oracle database.

    Sub Button1_Click()
    Dim Conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim Cmd As New ADODB.Command
    Dim sqlText As String
    Dim Row As Long
    Dim Findex As Long
    Dim Data As Worksheet
    Dim X As Long
    Dim UID As String
    Dim PWD As String
    Dim Server As String
    Dim strCon As String
    Application.Calculation = xlCalculationManual
    UID = "myUsername" 
    PWD = "myPassword" 
    Server = "myServerName" 'This comes from your TNSNames.ora file
    Set Data = Sheets("Sheet2") 
    Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=myServerName;Database=myDatabaseName;UID=myUsername;PWD=myPassword;"
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    ' Put your query next
    sqlText = " select * from Oracle_Table_Name "
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To 17 
    Data.Cells(1, X + 1) = RS.Fields(X).Name
    Do While Not RS.EOF
    Row = Row + 1
    For Findex = 0 To RS.Fields.Count - 1
    Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
    Next Findex
    Application.Calculation = xlCalculationAutomatic
    End Sub

    I have few cases as below:

    1. When Provider name is changed to "PROVIDER=MSDAORA.Oracle" ---> it throws error as Connection not Open.

    2. When Provider name is changed to "Driver={Microsoft ODBC Driver for Oracle}" ---> it throws error as [Microsoft] [ODBC Driver Manager] Data Source Name Not Found & no default driver specified.

    3. When Provider name is changed to "Provider=OraOLEDE.Oracle" ---> it throws error as Provider cannot be found. It may not be properly installed.

    I request to please help me from beginning to start this process. I would be very thankful to you. I am struck. Please help me. 


    Monday, January 26, 2015 8:06 AM