instead of setting command text directly, dima variable,
sat SQL as string then set that to your text.
Then .CommandText = SQL
is easier. The benefit is thatyou can examine the text
string created in immediate
?sql
or view locals.
You will see that there's no space after SELECT thus the
sql will break.

Yon want
SQL = _
"SELECT TESTREAD, TESTFREQ, TESTDATE," & _
" REPFREQ, REPDATE" & _
" FROM " & fname & _
" ORDER BY TESTREAD DESC")

you don't need to use the table name like X.Testread
since you're only using the one table

Your code would start
SELECTTABLE.TESTREAD....
if fbane was 'TABLE' since you don't have that space
after SELECT


Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
I found an extra set of quotes that got me thru the
syntax error:

..CommandText = Array("SELECT" & fname & ".TESTREAD," &
fname & ".TESTFREQ,"
& fname & ".TESTDATE," & fname & ".REPFREQ," & fname
& ".REPDATE" & Chr(13)
& "" & Chr(10) & "FROM " & fname & " " & fname & "& Chr
(13) &" & Chr(10) &
"ORDER BY " & fname & ".TESTREAD DESC")

but, now I get a SQL syntax error which points
to .Refresh
BackgroundQuery:=False
It doesn't matter if it is True or False. Any further
ideas?

"Mike Fogleman" wrote in
message
...
Can anyone see the syntax problem in this query in
the .CommandText line?
It
gives me a Compile Error: Expected: List separator
or ). I just don't see
it. Maybe a fresh pair of eyes can spot it.
Many thanks, Mike.

Sub Get_Data()
Dim fname

Sheets.Add
ActiveSheet.Name = "Database"
Range("A1").Select
fileToOpen = Application _
.GetOpenFilename("Database Files (*.dbf), *.dbf")
fname = Mid(fileToOpen, 9, 7)

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _


"ODBC;CollatingSequence=ASCII;DBQ=c:\LES5;Default Dir=c:\L
ES5;Deleted=0;Drive
r={Microsoft dBase Driver
(*.dbf)};DriverId=533;FIL=dBase" _
), Array( _
"

5.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout= 5;SafeTr
ansactions=0;Statis
tics=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array("SELECT" & fname
& ".TESTREAD," & fname &
".TESTFREQ," & fname & ".TESTDATE," & fname
& ".REPFREQ," & fname &
".REPDATE" & Chr(13) & "" & Chr(10) & "FROM " & fname
& " " & fname & " &
Chr(13) & "" & Chr(10) & "ORDER BY " & fname
& ".TESTREAD DESC")
.Name = fname
.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
End Sub




.