I too don't think you need Chr(13) & Chr(10) and what's the point in
aliasing your table (with its own name) if it's the only table? As you
have proved, adding such superfluous constructs causes confusion.
Instead try this:

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

--

"George Nicholson" wrote in message ...
"SELECT" should be "SELECT ". I'm not sure that a missing space would
classify as a List separator, so it may be something else.

"FROM " & fname & " " & fname & " & Chr(13) etc. appears to have an orphaned
" after the 2nd fname. That would through off the OpenQuote/CloseQuote
pairing that the compiler does and generate an error similar to what you are
getting.

I am unfamiliar with the Chr(13) & "" & Chr(10) construction used in a SQL
statement, but maybe I should get out more. I can't say that they are a
problem but I do wonder if they are gumming up the works. You might try
removing them, just make sure you leave a space preceding " FROM" and "
ORDERBY".
--
George Nicholson

Remove 'Junk' from return address.


"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(Arra y( _


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

5.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5 ;SafeTransactions=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