Excel 2010 Runtime Error 1004 General Odbc Error
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
Excel Runtime Error 1004 Application-defined Or Object-defined Error
site About Us Learn more about Stack Overflow the company Business Learn more excel 2013 runtime error 1004 about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Excel Runtime Error 1004 Unable To Set The Hidden Property Of The Range Class
Dismiss Join 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 excel runtime error 1004 pivot table - 1004 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 excel runtime error 1004 sort method of range class failed = "select top 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(
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 Runtime Error 1004 Macro
Excel, 2000), he gets the following error. Run Time Error '1004' General ODBC Error Obviously excel runtime error 1004 paste method of worksheet class failed some difference between the two machines, but who knows what to do to fix it? TIA Vaya con Dios, Chuck, CABGx3
Excel Runtime Error 1004 Autofill Method Of Range Class Failed
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 option in BA. go to VBA menu Tools - Options http://stackoverflow.com/questions/16151560/excel-2007-1004-run-time-error-refresh-query-table - 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. > > Run Time Error '1004' General ODBC Error > > Obviously some difference between the two machines, http://www.pcreview.co.uk/threads/run-time-error-1004-general-odbc-error.3666788/ 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 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 error
(עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) Home20132010Other VersionsLibraryForumsGallery Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: ODBC error Microsoft Office > Excel https://social.technet.microsoft.com/Forums/office/en-US/aa358a81-5720-4ddb-89d3-b044c238d664/odbc-error?forum=excel IT Pro Discussions Question 0 Sign in to vote Hi http://www.tek-tips.com/viewthread.cfm?qid=1619428 I have a problem with a excel document that gets external data from a MS SQL database. The problem happened when we moved the database to another server. Excel gets the data via FileDSN which I updated but the still I get the runtime error “ 1004 run error” General ODBC error “ and VB editor points at the row: Selection.QueryTable.Refresh BackgroundQuery:=False I would appreciate any help I can get Friday, June 04, 2010 9:45 AM Reply | Quote Answers 1 Sign in to vote Hi, The error occurs when problem occurs with accessing the original data, runtime error 1004 which is dependent on how that data was set up. You may want to make some attempt to refresh the table manually to see if that functions properly. The error you are getting indicates an error with the query connection. You may also re-connect the Excel file with the SQL server database. The Refresh method causes Microsoft Excel to connect to the data source of the QueryTable object, execute the SQL query, and return data to the range that is based on the QueryTable object. Unless this method is called, the QueryTable object doesn't communicate with the data source. When making the connection to the OLE DB or ODBC data source, Microsoft Excel uses the connection string specified by the Connection property. If the specified connection string is missing required values, dialog boxes will be displayed to prompt the user for the required information. If the DisplayAlerts property is False, dialog boxes aren't displayed and the Refresh method fails w
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. VBA Visual Basic for Applications (Microsoft) Forum at Tek-Tips HomeForumsProgrammersLanguagesVBA Visual Basic for Applications (Microsoft) Forum Run-time error '1004' General ODBC Error thread707-1619428 Forum Search FAQs Links MVPs Run-time error '1004' General ODBC Error Run-time error '1004' General ODBC Error Imakeoil (Programmer) (OP) 9 Sep 10 12:41 I am trying to get an ODBC connection to work from excel. I have copied the excel sheet from a working copy and am trying to point it at a different server. I am a VBA noob and have no idea why it isn't working. Sheets("DataQuery").Selectreportdate = ActiveSheet.Range("B1").ValueRange("A3").SelectWith Selection.QueryTable.Connection = Array(Array( _"ODBC;DSN=DSN201 ;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;DATABASE=Power;Network=" _), Array("DBMSSOCN;Address=111.11.221.70,1433")).Sql = Array(reportdate).Refresh BackgroundQuery:=FalseEnd WithI have not had any luck googling the '1004' error, it seems that it could be many things. Can anyone help, or point me in the direction of some good resources for setting the connection?Thanks RE: Run-time error '1004' General ODBC Error SkipVought (Programmer) 9 Sep 10 12:58 This is a very general error.Your SQL proabaly has an error.Please post your SQL.To verify, open the Immediate Window (see the View menu) and type in...CODE?Array(reportdate)and hit ENTER.COPY the resulting code and then on your sheet, EDIT the QueryTable and PASTE your copied SQL into the SQL window (see the SQL button in MS Query)If your SQL runs, then there is another problem. Skip,Just traded in