Excel Error 1004 Odbc
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss
Runtime Error 1004 General Odbc Error Excel 2010
the workings and policies of this site About Us Learn more run time error 1004 refresh backgroundquery false about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow run time error 1004 general odbc error in excel 2013 Questions Jobs Documentation Tags 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
Refresh Backgroundquery:=false Error
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 & " FROM [XXX].[ABCCustomer] As A" strSQL = strSQL & " Left join" strSQL
General Odbc Error 1004 Excel 2013
= 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 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
(עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) 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 to vote Hi I have a problem with a excel general odbc error tally document that gets external data from a MS SQL database. The problem happened when we
Microsoft Query General Odbc Error
moved the database to another server. Excel gets the data via FileDSN which I updated but the still I get the querytable.refresh error 1004 “ 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 http://stackoverflow.com/questions/27172202/run-time-error-1004-general-odbc-error-refresh-backgroundquery-false 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 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 https://social.technet.microsoft.com/Forums/office/en-US/aa358a81-5720-4ddb-89d3-b044c238d664/odbc-error?forum=excel 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 examining the value of the Connection property. After the database connection is made, the SQL query is validated. If the query isn't valid, the Refresh method fails with the SQL Syntax Error exception. Please take your time to try the suggestions and let me know the results at your earliest convenience. If anything is unclear or if there is anything I can do for you,
Question Need help? Post your question and https://bytes.com/topic/visual-basic/answers/765809-odbc-error-excel-macro-one-particular-pc get tips & solutions from a community of 418,548 IT Pros http://www.tek-tips.com/viewthread.cfm?qid=1619428 & 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 error 1004 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 general odbc error 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 5 Replies 100+ P: 491 daniel aristidou Since he said it might be date...which is possible
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 men