Error 1004 Odbc
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 About Us Learn more about run time error 1004 general odbc error excel 2007 Stack Overflow the company Business Learn more about hiring developers or posting ads with
Runtime Error 1004 General Odbc Error
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is run time error 1004 general odbc error refresh backgroundquery false 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 Run-time error Refresh Query Table up vote 1 down vote
General Odbc Error Refresh Backgroundquery
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 10 * from tableName" Dim S As Worksheet Set S = ActiveWorkbook.Sheets("Medical") With S.QueryTables.Add(Connection:=cn, run time error 1004 general odbc error in excel 2013 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 Apr 22 '13 at 17:51 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted Solution: All machines I was using with Exc
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
Run Time Error 1004 Refresh Backgroundquery False
the following error. Run Time Error '1004' General ODBC Error Obviously some difference between the
Refresh Backgroundquery False Error
two machines, but who knows what to do to fix it? TIA Vaya con Dios, Chuck, CABGx3 CLR, Nov 7, 2008 #1 general odbc error 1004 excel 2013 Advertisements Joel Guest It would help to know which line of code is failing. One thing to check is the error option in BA. go to VBA menu Tools - Options - General - Error Trapping. "CLR" wrote: http://stackoverflow.com/questions/16151560/excel-2007-1004-run-time-error-refresh-query-table > 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. > > Run Time Error '1004' General ODBC Error > > Obviously some difference between the two machines, but who knows what to do > to fix it? http://www.pcreview.co.uk/threads/run-time-error-1004-general-odbc-error.3666788/ > > 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 When failing, the bottom line, ".Refresh BackgroundQuery:=False" is the one that's highlighted.....but then that's the case with almost any error within this routine. My "Error Trapping" setting is "Break on unhandled errors".....if my User's computer has a different setting could that be the reason? (He's tied up right now so I can't chec
Forums Excel Questions General ODBC Error - Run time error 1004 Results 1 to 10 of 10 http://www.mrexcel.com/forum/excel-questions/433755-general-odbc-error-run-time-error-1004-a.html General ODBC Error - Run time error 1004This is a discussion on General ODBC Error - Run time error 1004 within the Excel Questions forums, part https://bytes.com/topic/visual-basic/answers/765809-odbc-error-excel-macro-one-particular-pc 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 error 1004 LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread 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 general odbc error 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 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
Question Need help? Post your question and get tips & solutions from a community of 418,502 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=False .FillAdjacentFormulas=False .PreserveFormatting=True .RefreshOnFileOpen=False .BackgroundQuery=True .RefreshStyle=xlInsertDeleteCells .SavePassword=False .SaveData=True .AdjustColumnWidth=True .RefreshPeriod=0 .PreserveColumnInfo=True .RefreshBackgroundQuery:=False Thanks for any help! Jan 31 '08 #1 Post Reply Share this Question