If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel VBA - Runtime Error "1004" General ODBC Error
Hi
I wrote a vba function that should return data to the excel spreadsheet after querying the Oracle database. I have installed and created a Microsoft ODBC connection for Oracle on my workstation. If I manually open excel workbook and navigate to Data, Get External Data, New Database Query, connect to the database, click on the SQL pushbutton and paste the query, click OK and hit the Return Data pushbutton, the query return data to the spreadsheet without any issues. I recorded all the above steps in a Macro and wrote a similar vba function for extracting data for another table. I then reopened another workbook, navigated to Tools, Macro, Visual Basic Editor and pasted the 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 in yellow: .Refresh BackgroundQuery:=False Here is the query I am using. This query reads data from all the Key fields of the same table from 2 database instances (source and target) and retrieves all the rows that are missing in either of the databases [(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 values of UID and PWD here purposefully. Here PSP2 and PSE2 are 2 seperate database instances. : ------------------------------------------------------------------------ Sub CompareMissingMacro1() With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER={Microsoft ODBC for 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 as to what is causing this error to occur. Pls let me know if you need more info. Thank you Siva --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
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 " wrote in message ... Hi I wrote a vba function that should return data to the excel spreadsheet after querying the Oracle database. I have installed and created a Microsoft ODBC connection for Oracle on my workstation. If I manually open excel workbook and navigate to Data, Get External Data, New Database Query, connect to the database, click on the SQL pushbutton and paste the query, click OK and hit the Return Data pushbutton, the query return data to the spreadsheet without any issues. I recorded all the above steps in a Macro and wrote a similar vba function for extracting data for another table. I then reopened another workbook, navigated to Tools, Macro, Visual Basic Editor and pasted the 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 in yellow: Refresh BackgroundQuery:=False Here is the query I am using. This query reads data from all the Key fields of the same table from 2 database instances (source and target) and retrieves all the rows that are missing in either of the databases [(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 values of UID and PWD here purposefully. Here PSP2 and PSE2 are 2 seperate database instances. : ------------------------------------------------------------------------ Sub CompareMissingMacro1() With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER={Microsoft ODBC for 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 as to what is causing this error to occur. Pls let me know if you need more info. Thank you Siva --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|