Excel Macro Runtime Error 1004 General Odbc Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions
Excel Macro Runtime Error 1004 Document Not Saved
you might have Meta Discuss the workings and policies of this excel macro runtime error 1004 paste method of worksheet class failed site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers excel macro runtime error 1004 application-defined or object-defined error or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community
Run Time Error 1004 General Odbc Error In Excel
of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up run time error 1004 general odbc error refresh backgroundquery false up vote 0 down vote favorite I have a working VBA code which runs as: wsEnd.Select Range("A:AQ").Delete strSQL = "Select * strSQL = strSQL
Run Time Error 1004 General Odbc Error Refresh Backgroundquery False
& " FROM [XXX].[ABCCustomer] As A" strSQL = strSQL & " Left join" strSQL = strSQL & " (Select * " strSQL = strSQL & " From [XXX]..[ABCCustomer]" strSQL = strSQL & " where LineageId = '123' ) B" strSQL = strSQL & " on a.product = b.product and a.[StartDate] = b.[StartDate]" strSQL = strSQL & " where (a.EndDate <> b.EndDate)" strSQL = strSQL & " and a.NewEndDate is NULL AND B.NewEndDate IS NULL" strSQL = strSQL & " and a.Id = '456" strSQL = strSQL & " order by b.ProductType" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DRIVER=SQL Server;SERVER=XXX\SQL01;UID=;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=XXX;DATA" _ ), Array("BASE=master")), Destination:=Range("$A$1")).QueryTable .CommandText = strSQL .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table_Query_from_XXX_C" .Refresh BackgroundQuery:=False End with, I have two other scripts starting after the End With but in
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 run time error 1004 his computer, (same versoion of Excel, 2000), he gets the following error. Run Time
Excel Vb Run Time Error 1004
Error '1004' General ODBC Error Obviously some difference between the two machines, but who knows what to do to fix general odbc error refresh backgroundquery 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/27172202/run-time-error-1004-general-odbc-error-refresh-backgroundquery-false 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
Forums Excel Questions General ODBC Error - Run time error 1004 Results 1 to 10 of 10 General ODBC Error - Run time http://www.mrexcel.com/forum/excel-questions/433755-general-odbc-error-run-time-error-1004-a.html error 1004This is a discussion on General ODBC Error - Run time https://bytes.com/topic/visual-basic/answers/765809-odbc-error-excel-macro-one-particular-pc 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 error 1004 to del.icio.usBookmark in TechnoratiTweet this thread Thread 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 time error 1004 have this working but I'm getting stuck on this 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 = Fals
Question Need help? Post your question and get tips & solutions from a community of 418,548 IT Pros & Developers. It's quick & easy. ODBC error in Excel macro, on one particular PC P: 13 elecooley Baically, myself and the person I sit next to use a shortcut to an Excel file. The Excel file has multiple macros. When office mate opens it, it works fine (ie all macros run, excel file gets data,). When i click on the shortcut, I get an error pop up: Run time error 1004' General ODBC error. When i click on Debug, VB opens and it shows: Object = General Procedure = Get_program_dates and highlighted in Yellow is: .Refresh BackgroundQuery:=False I have searched the internet for the error 1004 and found many solutions that dont work. The real strange thing is why it does not work on my machine, but on the guys next to me it does? We have checked that we have the same connections (Data Sources). Is this a problem with something else? Even the guy (not myself) who wrote the code can't figure out why it is not working. I found where someone said it might have to do with dates? But why does it work on his and not mine? Here is the snippet of code: Expand|Select|Wrap|Line Numbers WithActiveSheet.QueryTables.Add(Connection:=_ "ODBC;DSN=ADW;UID=CurUser;APP=MicrosoftOffice2003;WSID=CP040;DATABASE=DataWarehouse;Trusted_Connection=Yes"_ ,Destination:=Range("A1")) .CommandText=Array(_ "SELECTDISTINCTSTRAT_All_View.EvSDate"&Chr(13)&""&Chr(10)&"FROMDataWarehouse.dbo.STRAT_All_ViewSTRAT_All_View"&Chr(13)&""&Chr(10)&"WHERE(STRAT_All_View.EvSDate>{ts'2005-03-0100:00:00'})") '"SELECTDISTINCTSTRAT_All_View.EvSDate"&Chr(13)&""&Chr(10)&"FROMDataWarehouse.dbo.STRAT_All_ViewSTRAT_All_View"&Chr(13)&""&Chr(10)&"ORDERBYSTRAT_All_View.EvSDateDESC") .name="QueryfromADW" .FieldNames=True .RowNumbers=F