ODBC Syntax Problem

[Tolga]

I have synthax problem in one of the ODBC Database
queries. I would appreciate if someone can help me...

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=REPORTS_OK;Description=Connect to SQL-
Reports;UID=TOKTAY;PWD=toktay;APP=Microsoft Office
XP;WSID=TOKTAY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Controlling_without_stop_inv.Contract,
Controlling_without_stop_inv.A,
Controlling_without_stop_inv.Currency,
Controlling_without_stop_inv.Installments,
Controlling_without_stop_inv.Debt_ID, Su" _
, _
"m(Controlling_without_stop_inv.Principal),
Controlling_without_stop_inv.Activation_Date,
Controlling_without_stop_inv.Units,
Controlling_without_stop_inv.Vehicle_Group,
Controlling_without_stop_inv.de" _
, _
"bis_eff_int_rate_pa,
Controlling_without_stop_inv.Old_Contract_ID,
Controlling_without_stop_inv.Net_Investment"&chr(13)
&""&chr(10)&"FROM
reports.dbo.Controlling_without_stop_inv
Controlling_without_stop_inv"&chr(13)&""&chr(10)&"WHERE
(Contr" _
, _
"olling_without_stop_inv.A<>'*') AND
(Controlling_without_stop_inv.Payment_Due_Date>{ts '2004-
08-31 00:00:00'}) AND
(Controlling_without_stop_inv.Debt_ID='21') AND
(Controlling_without_stop_inv.Activat" _
, _
"ion_Date<={ts '2004-08-31 00:00:00'})"&chr(13)
&""&chr(10)&"GROUP BY
Controlling_without_stop_inv.Contract,
Controlling_without_stop_inv.A,
Controlling_without_stop_inv.Currency,
Controlling_without_stop_inv.Installments," _
,,)
.Name = "Query from REPORTS_OK"
.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

 

[Haldun Alay]

Hi,

I think you don't need to use chr(13) and chr(10) in your sql query.

for example

SELECT company, name, surname, age FROM MyTable WHERE age>30 GROUP BY company

is same with

SELECT company, name, surname, age
FROM MyTable
WHERE age>30
GROUP BY company

regards.


--
Haldun Alay
"Tolga" <[email protected]>, iletide sunu yazdi I have synthax problem in one of the ODBC Database
queries. I would appreciate if someone can help me...

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=REPORTS_OK;Description=Connect to SQL-
Reports;UID=TOKTAY;PWD=toktay;APP=Microsoft Office
XP;WSID=TOKTAY" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Controlling_without_stop_inv.Contract,
Controlling_without_stop_inv.A,
Controlling_without_stop_inv.Currency,
Controlling_without_stop_inv.Installments,
Controlling_without_stop_inv.Debt_ID, Su" _
, _
"m(Controlling_without_stop_inv.Principal),
Controlling_without_stop_inv.Activation_Date,
Controlling_without_stop_inv.Units,
Controlling_without_stop_inv.Vehicle_Group,
Controlling_without_stop_inv.de" _
, _
"bis_eff_int_rate_pa,
Controlling_without_stop_inv.Old_Contract_ID,
Controlling_without_stop_inv.Net_Investment"&chr(13)
&""&chr(10)&"FROM
reports.dbo.Controlling_without_stop_inv
Controlling_without_stop_inv"&chr(13)&""&chr(10)&"WHERE
(Contr" _
, _
"olling_without_stop_inv.A<>'*') AND
(Controlling_without_stop_inv.Payment_Due_Date>{ts '2004-
08-31 00:00:00'}) AND
(Controlling_without_stop_inv.Debt_ID='21') AND
(Controlling_without_stop_inv.Activat" _
, _
"ion_Date<={ts '2004-08-31 00:00:00'})"&chr(13)
&""&chr(10)&"GROUP BY
Controlling_without_stop_inv.Contract,
Controlling_without_stop_inv.A,
Controlling_without_stop_inv.Currency,
Controlling_without_stop_inv.Installments," _
,,)
.Name = "Query from REPORTS_OK"
.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

 

[Guest]

There is a comma missing:

reports.dbo.Controlling_without_stop_inv
Controlling_without_stop_inv"&chr(13)&""&chr(10)&"WHERE

should be

reports.dbo.Controlling_without_stop_inv,
Controlling_without_stop_inv"&chr(13)&""&chr(10)&"WHERE

This is extremetly messy. Consider:

reports.dbo.Controlling_without_stop_inv a,
Controlling_without_stop_inv b WHERE

Then replace all occurences of reports.dbo.Controlling_without_stop_inv. by
a. and that of Controlling_without_stop_inv b by b.: this will tidy up your
query.

Remove &chr(13) and &chr(10) as this does nothing in an SQL statement, only
helps the huma eye to read the statement. You are already using multi lines,
therefore the CR & LF are unnecessary.

 

[Jamie Collins]

I think you don't need to use chr(13) and chr(10) in your sql query.

Why? A carriage return is for the benefit of the human eye, not the
sql parser. Furthermore, there is a risk the parser will strip such
characters without replacing it with a space and could resulting in
incorrect syntax e.g.

"SELECT MyCol" & Chr$(13) & Chr$(10) & "FROM MyTable;"

is parsed as

SELECT MyColFROM MyTable;

Best to use a space character rather than a carriage return.

Jamie.

--

 

[Jamie Collins]

I think you don't need to use chr(13) and chr(10) in your sql query.

So we are in agreement then <g>. (I didn't see that little 'not' word
the first time!)

Jamie.

--