Excel Vba General Odbc Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site run time error 1004 general odbc error in excel 2010 About Us Learn more about Stack Overflow the company Business Learn more about
Run-time Error 1004 General Odbc Error
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join excel vba error 1004 application-defined or object-defined error the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel 2007 - 1004 excel vba error 1004 select method of range class failed Run-time error Refresh Query Table up vote 1 down vote favorite It appears that this error is so generic that many of the existing solutions address different issues. In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007. Here is a summary of the code: cn = "ODBC;Driver={SQL Server Native Client 10.0};Server=serverName;Database=dbName;Trusted_Connection=yes;" sql = "select top
Excel Vba Error 1004 Document Not Saved
10 * from tableName" Dim S As Worksheet Set S = ActiveWorkbook.Sheets("Medical") With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) .CommandText = sql .Refresh BackgroundQuery:=False End With This code executes perfectly in Excel 2010, but errors out in Excel 2007. Do I need to change the connection string structure for 2007, or is there another issue at work? excel vba odbc runtime share|improve this question edited May 6 '13 at 22:06 Undo♦ 18.4k157399 asked Apr 22 '13 at 16:03 lance 26115 Error 1004 is generic error but do you have the actual error text? Should say something like Runtime error 1004: followed by more description - that will help narrow down the problem. –AxGryndr Apr 22 '13 at 16:20 Run-time error '1004': General ODBC Error –lance Apr 22 '13 at 16:39 Is there a query in range B1 of the Medical sheet? If you right click in the cell you should see the refresh option. –AxGryndr Apr 22 '13 at 16:54 It does in 2010, but not in 2007. –lance Apr 22 '13 at 17:35 Instead of .Commandtext can you try changing S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) to S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"), Sql:=sql)? –AxGryndr
have a little macro that queries an ACCESS file and returns desired values. The program works fine on my computer, but when my user tries to run it on excel vba error 1004 method range of object _global failed his computer, (same versoion of Excel, 2000), he gets the following error. Run Time excel vba error 1004 autofilter method of range class failed Error '1004' General ODBC Error Obviously some difference between the two machines, but who knows what to do to fix
Excel Vba Error 1004 Sort Reference Not Valid
it? TIA Vaya con Dios, Chuck, CABGx3 CLR, Nov 7, 2008 #1 Advertisements Joel Guest It would help to know which line of code is failing. One thing to check is the error http://stackoverflow.com/questions/16151560/excel-2007-1004-run-time-error-refresh-query-table option in BA. go to VBA menu Tools - Options - General - Error Trapping. "CLR" wrote: > Hi All......... > I have a little macro that queries an ACCESS file and returns desired > values. The program works fine on my computer, but when my user tries to run > it on his computer, (same versoion of Excel, 2000), he gets the following > error. > > http://www.pcreview.co.uk/threads/run-time-error-1004-general-odbc-error.3666788/ Run Time Error '1004' General ODBC Error > > Obviously some difference between the two machines, but who knows what to do > to fix it? > > TIA > Vaya con Dios, > Chuck, CABGx3 > Joel, Nov 7, 2008 #2 Advertisements CLR Guest Hi Joel........ Thanks for the comeback..... Here's the code... With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & NewFile & ";DefaultDir= " _ ), Array( _ ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT `NPR Database`.`Disposition Date`, `NPR Database`.`Inspection Date`, `NPR Database`.`NPR Origin`, `NPR Database`.`NPR Number`, `NPR Database`.`Part Number`, `NPR Database`.`Serial Number`, `NPR" _ , _ " Database`.`Vendor Code`, `NPR Database`.`Vendor Name`, `NPR Database`.`No of Defects`, `NPR Database`.`Qty RTV`, `NPR Database`.`Defect Description`, `NPR Database`.`Corrective Action`" & Chr(13) & "" & Chr(10) & "FROM `" _ , _ "" & newfile2 & "`.`NPR Database` `NPR Database`" & Chr(13) & "" & Chr(10) & "ORDER BY `NPR Database`.`Vendor Code`" _ ) .Name = "Query from MS Access Database" .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 W
Forums Excel Questions General ODBC Error - Run time error 1004 Results 1 to 10 of 10 General ODBC Error - Run time error 1004This is http://www.mrexcel.com/forum/excel-questions/433755-general-odbc-error-run-time-error-1004-a.html a discussion on General ODBC Error - Run time error 1004 within the Excel Questions forums, part of the Question Forums category; Please help me before this laptop becomes a Frisbee! I almost have this working but I'm getting stuck on this ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread error 1004 Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Dec 3rd, 2009,12:58 PM #1 John Wood Board Regular Join Date Sep 2008 Location Ontario, Canada Posts 118 General ODBC Error - Run time error 1004 Please help me before this laptop becomes a Frisbee! I almost have this working but I'm getting stuck on this error excel vba error every time I execute it. I have an ODBC with read-only privileges and I recorded part of the code from the macro. The "from" and "to" date fields I added ( I think I'm correct). I would truly appreciate the help. Here's the code: Private Sub CommandButton1_Click() Dim smpldatea As Date Dim smpldateb As Date Dim fdate1 As String Dim fdate2 As String smpldatea = UserForm1.TextBox1.Text smpldateb = UserForm1.TextBox2.Text fdate1 = Format(smpldatea, "m/d/yyyy hh:mm:ss") fdate2 = Format(smpldateb, "m/d/yyyy hh:mm:ss") Sheets("Sheet1").Activate Range("A1:H10000").ClearContents With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DRIVER={Oracle in OraHome92};SERVER=XXXXXX;UID=DT_I;PWD=RO2ACCESS;DBQ=XXXXXXP;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" _ ), Array( _ ";FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT DT_CENTERLINE_AUDIT.AUDIT_DATE, DT_CENTERLINE_AUDIT.CENTERLINE_ID, DT_CENTERLINE_AUDIT.COMPLIANT, DT_CENTERLINE_AUDIT.CORRECTED, DT_CENTERLINE_AUDIT.LINE, DT_CENTERLINE_AUDIT.LOCATION" & Chr(13) & "" & Chr(10) & "FROM DTD" _ , _ "BA.DT_CENTERLINE_AUDIT DT_CENTERLINE_AUDIT" & Chr(13) & "" & Chr(10) & "WHERE (DT_CENTERLINE_AUDIT.AUDIT_DATE>= '" & fdate1 & "' And DT_CENTERLINE_AUDIT.AUDIT_DATE<= '" & fdate2 & "') AND (DT_CENTERLINE_AUDIT.L" _ , "OCATION='0902')") .Name = "Query from YODA2_2" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo =