Help with error 1004 in database query
I am writing a block of Visual Basic code for use with
Excel: With ActiveSheet.QueryTables.Add (Connection:=connectionString, _ Destination:=Range("A1")) .CommandText = SQLText .Name = DataSourceName & "Query" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With However, at the statement ".Refresh BackgroundQuery:=False", I got a run-time error 1004: Application-defined or object- defined error. The strange thing is that the same query worked well for a few days before ... Does anyone know what is the cause of the problem? How I can solve this problem? Please help! Thank you very much. Alex. |
Help with error 1004 in database query
Confirm that you can connect to the data source outside of Excel using the
same info as in the connection string, and that you can execute the query that is SQLText on that connection. Be sure the SQL that you run in your test is the same as what is in your code. The code adds the querytable, but the querytable doesn't execute (and you don't know if it is valid) until Refresh is called. My guess is that it fails because it can't execute due to some error in the definition. Another tactic: Try creating a similar query against the same datasource while recording a macro. If it succeeds, compare the recorded code to the original code. "Alex Zhang" wrote in message ... I am writing a block of Visual Basic code for use with Excel: With ActiveSheet.QueryTables.Add (Connection:=connectionString, _ Destination:=Range("A1")) .CommandText = SQLText .Name = DataSourceName & "Query" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With However, at the statement ".Refresh BackgroundQuery:=False", I got a run-time error 1004: Application-defined or object- defined error. The strange thing is that the same query worked well for a few days before ... Does anyone know what is the cause of the problem? How I can solve this problem? Please help! Thank you very much. Alex. |
All times are GMT +1. The time now is 03:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com