DB query syntax

[Mike Fogleman]

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;DefaultDir=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

 

[Mike Fogleman]

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?

 

[George Nicholson]

"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".

 

[Mike Fogleman]

Those extra Chr 10 & 13 came from the macro recorder. I will see if they can
be ommited. I got past the problem on that line (I think) but the query
still fails with an SQL syntax error.
Thanks, Mike

 

[Patrick Molloy]

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

 

[onedaywhen]

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")

--