How to execute query which has more then 25 lines.. from vb 2

[sqldevrequiretektips]

I want to run following query in command click event

VB 6.0 support only 25 physical lines lines joined with line-continuation...

so is there anybody can help me out...that how and where should i write this sql to execute...

any help would really appriciate..

INSERT INTO WLMod_Denormalized(CustLifeNo, WeekNo)
SELECT DISTINCT Cust_Life_Num, WeekNo FROM tbltblWLActCat_WLDay
--MONDAY
UPDATE WLMod_Denormalized
SET MON_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tbltblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='MON' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET MON_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tbltblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='MON' AND WLActCatId='O'
UPDATE Test_Denormalized
SET MON_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='MON' AND WLActCatId='D'

--TUESDAY
UPDATE WLMod_Denormalized
SET TUE_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='TUE' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET TUE_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='TUE' AND WLActCatId='O'
UPDATE Test_Denormalized
SET TUE_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='TUE' AND WLActCatId='D'

--WEDNESDAY
UPDATE WLMod_Denormalized
SET WED_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='WED' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET WED_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='WED' AND WLActCatId='O'
UPDATE Test_Denormalized
SET WED_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='WED' AND WLActCatId='D'

--THURSDAY
UPDATE WLMod_Denormalized
SET THU_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='THU' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET THU_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='THU' AND WLActCatId='O'
UPDATE Test_Denormalized
SET THU_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='THU' AND WLActCatId='D'

--FRIDAY
UPDATE WLMod_Denormalized
SET FRI_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='FRI' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET FRI_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='FRI' AND WLActCatId='O'
UPDATE WLMod_Denormalized
SET FRI_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='FRI' AND WLActCatId='D'

--SATURDAY
UPDATE WLMod_Denormalized
SET SAT_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SAT' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET SAT_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SAT' AND WLActCatId='O'
UPDATE Test_Denormalized
SET SAT_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SAT' AND WLActCatId='D'

--SUNDAY
UPDATE WLMod_Denormalized
SET SUN_Act_M='M'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SUN' AND WLActCatId='M'
UPDATE WLMod_Denormalized
SET SUN_Act_O='O'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SUN' AND WLActCatId='O'
UPDATE Test_Denormalized
SET SUN_Act_D='D'
FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E
ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo
WHERE WLDayID='SUN' AND WLActCatId='D'

 

[SonOfEmidec1100]

Break the string into logical chunks,eg by day then concatnate them.

string = "part1"
string = string & _
"part2"
string = string & _
"part3"
...

 

[JerryKlmns]

amardesai13, if I 'm getting it right, looks like you run 1 INSERT and 3 UPDATE sql statements for each day. Do you really need all-in-1 or could just run 4 separate sql statements every day?

The suggestion from SonOfEmidec1100

string = "part1"
string = string & _
"part2"
string = string & _
"part3"

isn 't much safe for security reasons(there is a thread about this), but it does work!

You could try something like

Dim theTableValue(2) As String
Dim theDayValue(6) As String * 3
Dim theSetValue(2) As String * 1
Dim theSqlStatement(2) As String
Dim iCount As Integer

theTableValue(0) = "WLMod_Denormalized"
theTableValue(1) = "WLMod_Denormalized"
theTableValue(2) = "Test_Denormalized"
theSetValue(0) = "M"
theSetValue(1) = "O"
theSetValue(2) = "D"
theDayValue(0) = "MON"
    .
    .
    .
theDayValue(6) = "SUN"

'Select the appropiate value for theDayValue first

'and the relevant sql statement is created here
For iCount = 0 To 2
theSqlStatement(iCount) = "Update " & theTableValue & _
    " SET " & theDayValue & "_Act_M= '" & theSetValue & "'" & _
    "FROM WLMod_Denormalized D INNER JOIN tblWLActCat_WLDay E " & _
    "ON D.CustLifeNo=E.Cust_Life_Num AND D.WeekNo=E.WeekNo " & _
    "WHERE WLDayID='" & theDayValue & "' AND WLActCatId= '" & theSetValue & "';"

Next iCount 

[\code]

a bit lengthly but choice is yours.

 

[bassguy]

Why hasn't anyone said Stored procedure?

 

[sqldevrequiretektips]

I am running this code from VB front end and all the tables i am using they are all local Ms Access tables..

So, I guess that's the reason..
AD

 

[bassguy]

ahhh er i mean ew

http://www access

lol
bassguy