Ask a Question Sign up for Free 171 Experts currently online. Ask Questions for Free!

Excel VBA - Runtime Error "1004" General ODBC Error - Microsoft Excel

Hi I wrote a vba function that should return data to the excel spreadshee after querying the Oracle database. I have installed and created Microsoft ODBC connection for Oracle on my workstation. If I manually open excel workbook and navigate to Data, Get Externa Data, New Database Query, connect to ...

Results 1 to 3 of 3

  1. #1

    Excel VBA - Runtime Error "1004" General ODBC Error

    Hi

    I wrote a vba function that should return data to the excel spreadshee
    after querying the Oracle database. I have installed and created
    Microsoft ODBC connection for Oracle on my workstation.

    If I manually open excel workbook and navigate to Data, Get Externa
    Data, New Database Query, connect to the database, click on the SQ
    pushbutton and paste the query, click OK and hit the Return Dat
    pushbutton, the query return data to the spreadsheet without an
    issues.

    I recorded all the above steps in a Macro and wrote a similar vb
    function for extracting data for another table. I then reopened anothe
    workbook, navigated to Tools, Macro, Visual Basic Editor and pasted th
    vba function that I wrote over there and executed it.

    It then gives me this error "Run Time Error '1004' General ODBC Error"
    When I click on the Debug, it highlights the following line of code i
    yellow:
    .Refresh BackgroundQuery:=False

    Here is the query I am using. This query reads data from all the Ke
    fields of the same table from 2 database instances (source and target
    and retrieves all the rows that are missing in either of the database
    [(Source - Target) + (Target - Source)]:
    ---------------------------------------------------------------------------
    Select
    'In PSP2 and not in PSE2' INSTANCES,
    ACTION,
    ACTION_REASON,
    EFFDT
    from PS_ACTN_REASON_TBL

    MINUS

    Select
    'In PSP2 and not in PSE2' INSTANCES,
    ACTION,
    ACTION_REASON,
    EFFDT
    from PS_ACTN_REASON_TBL@COMPARE_PSE2

    UNION ALL

    Select
    'In PSE2 and not in PSP2' INSTANCES,
    ACTION,
    ACTION_REASON,
    EFFDT
    from PS_ACTN_REASON_TBL@COMPARE_PSE2

    MINUS

    Select
    'In PSE2 and not in PSP2' INSTANCES,
    ACTION,
    ACTION_REASON,
    EFFDT
    from PS_ACTN_REASON_TBL



    Below is the Macro I wrote to execute this query. I masked the value
    of UID and PWD here purposefully. Here PSP2 and PSE2 are 2 seperat
    database instances. :
    ------------------------------------------------------------------------
    Sub CompareMissingMacro1()

    With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DRIVER={Microsoft ODBC fo
    Oracle};UID=<userid>;PWD=<password>;SERVER=PSP2;", _
    Destination:=Range("A1"))
    .CommandText = Array( _
    "Select 'In PSP2 and not in PSE1' INSTANCES, ACTION
    ACTION_REASON, EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10
    _
    , _
    "MINUS" & Chr(13) & "" & Chr(10) _
    , _
    "Select 'In PSP2 and not in PSE1' INSTANCES, ACTION
    ACTION_REASON, EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13)
    "" & Chr(10) _
    , _
    "UNION ALL" & Chr(13) & "" & Chr(10) _
    , _
    "Select 'In PSE1 and not in PSP2' INSTANCES, ACTION
    ACTION_REASON, EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13)
    "" & Chr(10) _
    , _
    "MINUS" & Chr(13) & "" & Chr(10) _
    , _
    "Select 'In PSE1 and not in PSP2' INSTANCES, ACTION
    ACTION_REASON, EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10
    _
    )
    .Name = "Query from ora_psp2"
    .FieldNames = True
    .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
    End With
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "ACTN_REASON_TBL"
    End Sub

    ----------------------------------------------------------------------------

    I would appreciate if anyone can look into this and shred some light a
    to what is causing this error to occur. Pls let me know if you nee
    more info.

    Thank you
    Siv

    --
    Message posted from http://www.ExcelForum.com

    SKS
  2. #2

    Re: Excel VBA - Runtime Error "1004" General ODBC Error

    I probably shuldn't be responding to this question, since I've never made an
    ODBC connection to a database before, but...
    Do you need a defined name (range) in your new spreadhseet called "Query
    from ora_psp2"?

    "SKS >" <<com> wrote in message
    news:com... 

    -- 


    Trevor
  3. #3

    Re: Re: Excel VBA - Runtime Error "1004" General ODBC Error

    Hi Trevor,

    I am not 100% sure if I have to use a predefined name. To connect t
    the database, I have to use an existing data source. As I mentioned i
    the main post, I initially recorded a Macro. I later cloned this macr
    to generate a similar output in order to eliminate all the manual step
    involved in the above process. That's the reason why I use that line o
    code.

    Thanks
    SK

    --
    Message posted from http://www.ExcelForum.com

    SKS

Similar Threads

  1. Replies: 3
    Last Post: 06-30, 11:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100