Help with error 1004 in database query

[Alex Zhang]

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.

 

[Bob Kilmer]

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.