ODBC Syntax Problem
 

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(1 3)
&""&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

ODBC Syntax Problem
 

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 age30 GROUP BY company

is same with

SELECT company, name, surname, age
FROM MyTable
WHERE age30
GROUP BY company

regards.


--
Haldun Alay
"Tolga" , 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(1 3)
&""&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

ODBC Syntax Problem
 

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.

"Tolga" wrote:

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(1 3)
&""&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

ODBC Syntax Problem
 

"Haldun Alay" <haldunalayATyahooDOTcom wrote ...

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.

--

ODBC Syntax Problem
 

"Haldun Alay" <haldunalayATyahooDOTcom actually did write ...

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.

--