Excel Runtime 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
Runtime Error 1004 General Odbc Error
policies of this site About Us Learn more about Stack Overflow the run time error 1004 general odbc error in excel company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags excel runtime error 1004 application-defined or object-defined error Users Badges Ask Question x 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
Excel 2013 Runtime Error 1004
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 & " FROM [XXX].[ABCCustomer] As A" strSQL = strSQL & " Left join" strSQL = strSQL & " (Select * " strSQL
Excel Runtime Error 1004 Pivot Table
= 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 inside the same sub all using the same VBA just different SQL, which all work perfectly fine. Then I have this very annoying forth, which is causing my a real headache, that goes as follows: strSQL = "Select * strSQL = strSQL & " FRO
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 excel runtime error 1004 sort method of range class failed on his computer, (same versoion of Excel, 2000), he gets the following error.
Excel Runtime Error 1004 Macro
Run Time Error '1004' General ODBC Error Obviously some difference between the two machines, but who knows what to do excel runtime error 1004 paste method of worksheet class failed to fix 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 http://stackoverflow.com/questions/27172202/run-time-error-1004-general-odbc-error-refresh-backgroundquery-false the error 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 > http://www.pcreview.co.uk/threads/run-time-error-1004-general-odbc-error.3666788/ error. > > 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 .PreserveColum
(עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) 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 IT Pro Discussions Question 0 Sign in https://social.technet.microsoft.com/Forums/office/en-US/aa358a81-5720-4ddb-89d3-b044c238d664/odbc-error?forum=excel to vote Hi 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 http://www.tek-tips.com/viewthread.cfm?qid=1619428 server. Excel gets the data via FileDSN which I updated but the still I get the “ 1004 run error” General ODBC error “ and VB editor points at the error 1004 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, which is dependent on how that data was set up. You may want to make some attempt to refresh the table manually to time error 1004 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 with the Insufficient Connection Information exception. After Microsoft Excel makes a successful connection, it stores the completed connection string so that prompts won't be displayed for subsequent calls to the Refresh method during the same editing session. You can obtain the completed connection string by exami
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