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

0
6/21/2004 9:24:05 PM
excel.newusers 15348 articles. 1 followers. Follow

1 Replies
778 Views

Similar Articles

[PageSpeed] 13

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 >" <<SKS.187z63@excelforum-nospam.com> wrote in message
news:SKS.187z63@excelforum-nospam.com...
> 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/
>


0
tsides (114)
6/22/2004 5:45:51 PM
Reply:

Similar Artilces:

Help with Run-Time error 1004
We are currently using Excel 2003 and IE 7.0. We have a daily budget spreadsheet that is posted on our internal "staffweb" intranet. Staff access this daily.xls spreadsheet via IE. This spreadsheet contains extensive macros and pivot tables. This file is stored on a mapped network drive for our users - something like "F:\FINADMIN\Budget\TheDaily\The_Daily.xls" When a person double-clicks on this hyperlink from IE - Excel launches correctly - you see a message about enabling macros and the pivot tables and macros all work just fine. When another person say a few minutes...

Error 1004 Method 'Range' of object 'Global' failed
Hi I am being plagued by a problem which I cannot resolve, so I turn to the Repository Of All Knowledge..... I have a procedure in a VB application (not within Excel, but an application I've created myself). It contains the following code: Dim xl As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim rng As Excel.Range Dim x As Integer Set xl = New Excel.Application xl.Visible = True Set wb = xl.Workbooks.Add Set ws = wb.Worksheets(1) Set rng = ws.Range("A1") For x = wb.Worksheets.Count To 2 Step -1 wb.Worksheets(x).Delete Next x For Each fld In r...

Error 1004: Unable to get the axis property
Hello! I have this code, and it is supposed to drow graphs dynamicly from access table to excel. It does it ok, but when I close excel I get message "Problem ""CreateXLChart() Error 1004: Unable to get the Axes Property of the Chart class". Does anyone know why, and what I should to to stop getting it? Thanks! Private Sub Command24_Click() On Error GoTo ErrorHandler Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") Dim wb, wb1 As Object Dim db As Database Dim rs As DAO.Recordset Dim i, j, k As Integer Dim iRowCount As Integer Dim iBorder As Int...

Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values =
Next Problem is I now get a Run-Time Error 1004 on ActiveChart.SeriesCollection part of my program. I double checked the variable names and everything is consistant. It appears that I can not use a varible name in the .Values and .XValues statements. The error code explaination is not very helpful. My code is below. Again thanks for your help. Sub CreateNewSortRange() Dim TotalsRange As Range Dim TotalsCell As Range Dim CategoryRange As Range Dim CategoryCell As Range Dim StartTotalsAddress As String Dim EndTotalsAddress As String Dim StartCategoryAddress As String ...

Error 1004 using not contiguous data
Preface: sorry for my english. :) I faced with an annoying problem while chosing as "SeriesCollection" a not contiguous range in the same row. I don't know if i'm using the right word, the result of the recording of the desidered behaviour is: ActiveChart.SeriesCollection(1).Values = "=(temp!R1C4,temp!R1C6,temp!R1C8)" This is wonderful and works fine. Actually my macro works with dynamic data, that's why i cannot use this fixed formula. So, my Sub creates the desidered range using a series of such a cycle: Dim rZona(5) As String Dim rZ(5) As Range If (h...

Error 1004 when setting range value
I keep getting that Error 1004: Application-defined or object-defined error, when it gets to line: --->Range("res_REVIEWBY").Value = "love" WHYYYY? ----------------------------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'On Error Resume Next On Error GoTo ErrHandler Dim myDoc As Worksheet Set myDoc = ActiveSheet If (Target.Cells.Row >= 5 And Target.Cells.Row <= 6) And Target.Cells.Column = 3 Then Range("res_REVIEWBY").Value = "love" ...

Run time error 1004, General ODBC error
Hi- I've seen other people ask similar questions, but haven't seen an answer. I recorded a macro, and it has run fine dozens of times, but all of a sudden I'm getting a "run time error 1004, general ODBC error" message. When I go to debug, it shoes me the line: ..Refresh BackgroundQuery:=False I don't know how to program in the macro language, I just recorded this. I've searched all over the net for an answer, but have never found one, other than the suggestion that perhaps the worksheet I'm searching is too large (but no solution was proposed). Thanks!...

Run-time error 1004 on border grid
Hi, I had an application that download data to Excel spreadsheet. Using the same application, I was given run-time error '1004': "Unable to set the LineStyle property of the Border class" on some PCs and on others PCs are running fine. When debug, the error was at line area ".Borders(xlInsideHorizontal).LineStyle". I suppose it was the PC setting that differ but could not find out where. All PCs are installed with Excel 2003 SP3. Please advise. Thanks. The inside horizontal border will only be available if you have at least two contiguous (vertical) cells ...

wupdmgr.exe Application Error Event ID 1000, 1004
Greetings, I have problem on many different machines with Windows XP Professional editions within the same network: on logon I receive the message: The instruction at "0x004021c0" referenced memory at "0x8240428". The memory could not be "read" for wupdmgr.exe. Event logs on the machines are just stuffed up with Events 1000 and 1004 Application Errors for wupdmgr.exe. My AV software is KIS2010 and most of these computers are fresh Windows XP installations. My AV software didnt find any viruses or trojans, hardware requirements for operating system...

error 1004 when assigning an array to a range
Hello, The following is a piece of code that I inherited and that I'm trying to optimize. In short it does the following: * transform the value of each cell in a template range and put the result in an array * assign the array to a result range * iterate trough the range, and set each cell's comment to the cell's value * again, transform the value of each cell in another template range and put the result in an array * assign the array to the same result range ' GENERATE ALL VERTICAL DATAS Dim rng As Range Set rng = Range(Cells(Ld_Recurrent, ...

Error 1004
Hi , Any help is appreciated. It was working fine on another Data set and all i did is to change the List values and now I am getting this error : Run Time Error '1004' Unable to Set _Default property on PivotItem class background: I am running a loop where it looks at a list of values on another worksheet and Pivot Tables Runs through the loop and copies and paste the Pivot Result Set in another worksheet and Goes to the next Value in the list and so on. here is my Code: Dim pvtField As PivotField Dim iCount As Integer 'SM add pastefromstartcol...

Macro Issue
My VBA skills are limited to using recorded macros, not writing them- so when something won't execute... well, I'm lost. I painstakingly recorded my actions importing 38 small text files, including a clear contents command so that the workbook can be re-used. I have done this successfully in the past. The macro worked after creation once, but not after a save/close workbook. It now stalls at ' .Refresh BackgroundQuery:=False' of the first import as displayed below, with 'run time error 1004 - Application Defined or object - defined error Sub DataImport() ' &...

VC++ .NET 2003: XmlTextReader Errors C2144 & C1004
Hi all, I read the Microsoft KB Q815658 "How to read the XML data from a file using Visual C++ .NET" and tried to follow the steps of executing its source code in my VC++ .NET 2003 under Console Application (.NET). I lauched a project 'Q815658XMLRead' (see the attached .cpp file below) and did the "Build" and I got the following 2 errors: c:\Documents and Settings\Scott H. Chang\My Documents\Visual Studio Projects\Q815658XMLRead\Q815658XMLRead.cpp(13): error C2144: syntax error : 'int' should be preceded by ';' c:\Documents and Settings\Scott...

Error 1004 when opening a workbook
Dear all, I have EXCEL 2003 and everytime I try and open a file I get Error 1004 "Cannot edit a macro on a hidden workbook. Unhide the workbook using the unhide command". I've gone to the help and done what it says - Set to trust access to Visual Basic Project - but it doesn't do any good. Has anyone got an idea of how to fix this rather irritating problem? many thanks, neil ...

EXCEL VBA
run-time error 1004 - Copy method of sheets class failed. Sheets("All_Reg_Data").Select Dim i_WHY_AM_I_5 As Integer i_WHY_AM_I_5 = 5 Range(Cells(1, i_WHY_AM_I_5), Cells(1, i_TotalNumberOfColumns - 1)).Select Selection.Copy Sheets("Reg_Data").Select Cells(1, i_WHY_AM_I_5).Select ActiveSheet.Paste Sheets(Array("Reg_Data", "Reg")).Select Sheets("Reg").Activate Sheets(Array("Reg_Data", "Reg")).Copy I received the error on the final line. The table consists of 6 sheets. The Reg_data is a table of Data, the Reg is a graph. No...

Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai
-------------------------------------------------------------------------------- Hello, I am trying to generate a chart with multiple series. The number o series is not fixed and can be any number. I recorded a macro and the modified the code. 'Counter' refers to the number of the series and have referenced it in a worksheet. I am getting the error in the lin that is bold. Thank you for your help. The code is as follows: Dim Counter As Integer Dim i As Integer Sheets("With TC").Select Counter = Cells(38, 13).Value ActiveChart.ChartType = xlLine With Sheets("VP_Wit...

Run-time error 1004
I am using Excel 97 on NT and have a command button on a sheet to whic I attached a macro for copying the sheet to a new workbook, deletin specific columns and other unnecessary info, and saving the new book t a network drive under a name with today's date for emailing to anothe area. When I perform the function manually everything works beautifully bu when I attempt to execute the macro I get the following error "Run-time error '1004': Copy method of worksheet class failed". when use the Debug button, VBA highlights Sheets("Daily").Copy as th culprit. Macro f...

Error:11004Number:800CCCOD IN OUTLOOK EXP.
ERRORS:The host'smtp3.nb.sympatico.ca could not be found.I've verifyed my server name & password correctly.Account:po3.nb sympatico.ca' protocal:SMTP'Port:25 secure(SSL):No socket error:11004 number:0x800CCC0D. Need help dot no what to do. ...

Copying and pasting values error 1004
Whenever i run this piece of code i get the error "paste method of worksheet class failed" runtime error 1004 This is in excel 2003 and i did not have this problem in 2000. Coul anyone shed any light on this?? Range("A1").Select Sheets("atl").Select Range("A1").Select Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=9, Criteria1:="1" Selection.AutoFilter Field:=13, Criteria1:="3" Columns("J:J").Select Range("J:J,B:B").Select Selection.Copy Sh...

Run-time Error 1004
I have a macro which splits composite data (20,000+ rows) into a number of accounts (3 at present) and then for each of these accounts splits the data further into users (85, 156, 130). Having separated the data, I copy each part to a temporary sheet, move this away from the main workbook and then save the new workbook with a filename based on the username. The problem is that when I run it it seems to stop (having created 177 new files) with this error message "Copy method of Worksheet class failed". The actual statement pointed to by debug is Sheets(my_temp).Copy After:=S...

Unable to set the Name Property of the Series Class : Error '1004'
I have an application that builds automatic QC graphs from imported data from a user database (another excel file). This one row of data is then plotted on a graph with its specific upper and lower limit values My problem is that the specific data changes depending on the type of data, each type has its own upper and lower limits. I can get the chart to import the type of data that I select in a combo box, and the limits values will then be grouped specifically. I get the error when I want to display the graph (the code below is part of what builds the graph) and change the names of the ser...

Bug: VBA Range.Copy throws Error 1004 when Form visible
I am reporting a VBA bug. I hope this is the right place to do that. I'm in VBA writing macros for Excel 2004 with Mac OS X. It seems that an Error (code 1004 I think) gets thrown whenever the Copy method of a Range gets called while a Form is visible. The Copy method works if I hide the form before using it. For example, in code for a VBA form in an Excel workbook: Private Sub CommandButton2_Click() Worksheets(1).Range("A1").Copy ' Throws Error 1004 End Sub Private Sub CommandButton2_Click() Me.Hide Worksheets(1).Range("A1").Copy ' Works, n...

Excel export to Access
I have Office 2003 and would like to export cell contents to an Access table I've created using an Excel worksheet button. The code below works when there are only 14 cells but bigger than that I get "method range of object worksheet failed. Run-time error 1004." arrrg. Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData FY2010.mdb" ' Replace actual Access file p...

command line error MIDL1004 : cannot execute C preprocessor cl.exe
Hello, I have been working on VS2005 VC++ projects for a few weeks. Now I got the following compiler error. There are 62 projects in the solution but only about 10 projects have this compiler error. command line error MIDL1004 : cannot execute C preprocessor cl.exe midl If I go to BuildLog.htm and follow the instruction there to run the command lines they work fine. I don't know why VS 2005 could not compile. I also tried to copy my coworks environment variables. I still got the same error for same projects. Any one see this error before? How do you make it go away?...

Excel 1004 Error When Pasting Special W/ Macro
Hi all, I'm looking for some help with this macro...it has me really confused because it was working earlier, but now it hangs on this line of code (it is asterisked below) Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False This macro runs when the workbook opens and copies the contents of one sheet and pastes only the values to another sheet. The range that is being copied from and pasted to are both the same size. I have also tried this by selecting the upper-leftmost cell in the area I want to paste to but it has ...