Changing fixed date with a variable in a ODBC Query

[Hande & Tolga]

Below is the original code we use in our VB to retrieve
data directly from database. Instead of using a fixed
activation date "2003-09-17 00:00:00" (please check the
location in the code below) we want to replace that with
a variable where it makes the code more flexible such as
Temp1 where we enter or define the date to Temp1 earlier
in the code. We would appreciate if someone could help
us with the syntax.

Actual Code
-----------

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=AAA_BBBBBBB;UID=TTTTTTT;PWD=TTTTTTT;APP=Micr
osoft Office XP;WSID=TTTTTTT;DATABASE=reports" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Controlling_stop_invoice.Contract,
Controlling_stop_invoice.A,
Controlling_stop_invoice.Currency,
Controlling_stop_invoice.Payment_Due_Date,
Controlling_stop_invoice.Debt_ID, Controlling_stop_i" _
, _
"nvoice.Principal,
Controlling_stop_invoice.Activation_Date,
Controlling_stop_invoice.Units,
Controlling_stop_invoice.Vehicle_Group" & Chr(13) & "" &
Chr(10) & "FROM reports.dbo.Controlling_stop_invoice
Controlling_stop_invoice" & Chr(13) & "" & Chr(10) & "" _
, _
"WHERE (Controlling_stop_invoice.Debt_ID='21')
AND (Controlling_stop_invoice.Activation_Date<={ts '2003-
09-17 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY
Controlling_stop_invoice.Contract" _
)
.Name = "Query from reports_ok_20"
.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

 

[keepitcool]

Working with queries the strings are getting very long and
difficult to read.

To keep rour CODE readable, try splitting the lines and the components
of the commandstring.

For read & EDITability, I've tried to split up the COMMAND String.
and used SQL's alias feature to shorten the table name in the from
clause.

Linefeeds are NOT needed to the SQL parser and I've left them out.
(but DO note I've included spaces around the keywords.

I contruct the sComm string in the beginning in order to isolate
string manipulation problems from the query building and make the code
easier to debug. use DEBUG.print sComm to check your total string in the
IMMEDIATE pane.


Sub Step1_Editability()
Dim sConn$, sCommand$
Dim sSELECT, sFROM, sWHERE, sGROUP, sORDER

sConn = "ODBC;DRIVER=SQL Server;SERVER=AAA_BBBBBBB;" & _
"UID=TTTTTTT;PWD=TTTTTTT;APP=Microsoft Office XP;" & _
"WSID=TTTTTTT;DATABASE=reports"

sSELECT = " SELECT " & _
"CSI.Contract,CSI.A,CSI.Currency,CSI.Payment_Due_Date," & _
"CSI.Debt_ID,CSI.Principal,CSI.Activation_Date," & _
"CSI.Units,CSI.Vehicle_Group "
sFROM = " FROM " & _
"reports.dbo.Controlling_stop_invoice CSI"
sWHERE = " WHERE " & _
"CSI.Debt_ID='21') " & _
"AND (CSI.Activation_Date<={ts '2003-09-17 00:00:00'})"
sGROUP = ""
sORDER = " ORDER BY " & _
"CSI.Contract"

sComm = sSELECT & sFROM & sWHERE & sGROUP & sORDER


With ActiveSheet.QueryTables.Add( _
Connection:=sConn, Destination:=Range("A1"))

..CommandText = sComm
.Name = "Query from reports_ok_20"
.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

Application.DisplayAlerts = True
End Sub

Sub Step2_Flexibility()
Dim sConn$, sCommand$
Dim sS$, sF$, sW$, sG$, sO$

'same as above, WITH following
Dim sID$, sDate$
sID = 21
sDate = Format(Now, "yyyy\-mm\-dd hh\:mm")

sW = " WHERE " & _
"CSI.Debt_ID='" & sID & _
"') AND (CSI.Activation_Date<={ts '" & _
sDate & "'})"

End Sub






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >