DB query syntax
 

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

DB query syntax
 

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(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

DB query syntax
 

"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

DB query syntax
 

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

DB query syntax
 

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




.

DB query syntax
 

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