+ Reply to Thread
Results 1 to 6 of 6

Excel query to Oracle DB

  1. #1
    Registered User
    Join Date
    11-10-2012
    Location
    Watford, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Excel query to Oracle DB

    My requirement is to run a SQL query on Oracle DB and return the results obtained from it into an XLS. I am new to VB programming and have searched for content on the internet and have put together the code as below but it is not working and failing with error message

    Run time error 3709. The connection cannot be used to perform this operation. It is closed or invalid in context.

    But when i execute the SQL query on Oracle DB it runs fine and gives me results. When i hit debug it highlights the line Set RS = Cmd.Execute. Please help

    Sub TEST_Connection()


    Dim con As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim query As String
    Set con = New ADODB.Connection
    Set RS = 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



    strCon = "Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(DESCRIPTION=" & _
    "(ADDRESS=(PROTOCOL=TCP)" & _
    "(HOST=xxxxx)(PORT=xxxxx))" & _
    "(CONNECT_DATA=(SID=xxxxx))); uid=xxxxx; pwd=xxxxx;"

    con.Open (strCon)



    Set Data = Sheets("Sheet1")
    Data.Select
    Range("A:F").ClearContents


    Cmd.CommandType = adCmdText
    sqlText = "select request_id from pom__products where rownum <3"

    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 0 To 10
    Data.Cells(1, X + 1) = RS.Fields(X).Name
    Next
    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
    RS.MoveNext
    Loop
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    End Sub

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Excel query to Oracle DB

    This is way out of my comfort zone, but when I look at your code I do not see a relation between the open connection and the sql command executed to create the record set. I would logically expect to see something like:

    Please Login or Register  to view this content.
    i.e. the RS is opened against the open connection
    If you like my contribution click the star icon!

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel query to Oracle DB

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    11-10-2012
    Location
    Watford, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Excel query to Oracle DB

    I am getting an error . Item cannot be found in the collection for the requested name or ordinal. Please help

    Please Login or Register  to view this content.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Excel query to Oracle DB

    you're trying to return two field names but you only selected one field
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-10-2012
    Location
    Watford, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Excel query to Oracle DB

    Perfect! Thanks a lot Joseph

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1