create view in sql server DB using excel VBA

if you just want to create the view so you can use it in the query, then you are better of using CTE
CTE lets you define a view that only live for a single query, for example:

with my_cte (col1 , col2) as
(select col1, col2 from my_table where col1=1)
select * from my_cte
where col2 =2

Where this code is written?
under, VBA of excel ?
what references should be loaded to VBA ?
Do you have sample written in VBA?
please advice.
Thank you

this code -
"with my_cte (col1 , col2) as
(select col1, col2 from my_table where col1=1)
select * from my_cte
where col2 =2"

is a single sql statement, so you just use it as you use any other sql statement

this is the view & query
I'm getting en error "worng syntex near with"
 sql = ""
  sql = sql & "SELECT "
  sql = sql & " TDisplay.MMItem, "
  sql = sql & " TDisplay.TransType, "
  sql = sql & " TDisplay.TransQty, "
  sql = sql & " TDisplay.DemandNxtlvl, "
  sql = sql & " TDisplay.ToDate "
  sql = sql & "FROM TDisplay "
  sql = sql & "WHERE "
  sql = sql & " TDisplay.TransQty < 0 "
 
  Select Case Rep_Filter
    Case "ALL"
    Case "FGI"
      sql = sql & " AND TDisplay.ItemBomLvl = 1   "
    Case "FgiBY"
      sql = sql & " AND TDisplay.ItemBomLvl = 1   "
      sql = sql & " AND TDisplay.MMItem = TDisplay.MMSubItem "
    Case "MK"
      sql = sql & " AND TDisplay.ItemBomLvl > 1   "
      sql = sql & " AND TDisplay.ItemBomLvl < 6   "
    Case "BY"
      sql = sql & " AND TDisplay.ItemBomLvl = 6   "
  End Select
  sql = sql & " AND TDisplay.DemandNxtlvl > 0 "
  sql = sql & ";"
  sql2 = ""

 ' -----------view needed

  sql2 = sql2 & "SELECT "
  sql2 = sql2 & " SumDmnd0.MMItem, "
  sql2 = sql2 & " IIf(Year([ToDate])*100+Month([ToDate])< "
  sql2 = sql2 & "     Year(Date())*100+Month(Date()),"
  sql2 = sql2 & "     Year(Date())*100+Month(Date()), "
  sql2 = sql2 & "     Year([ToDate])*100+Month([ToDate])) AS MntDmnd, "
  sql2 = sql2 & "     Sum(SumDmnd0.DemandNxtlvl) AS DmndNxtlvl, "
  sql2 = sql2 & "     First(TItems.HanItem) AS HanItem, "
  sql2 = sql2 & "     First(TItems.MrpCtrl) AS MrpCtrl, "
  sql2 = sql2 & "     First(TPlanParam.ActiveForPO) AS ActiveForPO "
  sql2 = sql2 & "FROM (SumDmnd0 "
  sql2 = sql2 & " INNER JOIN TItems ON SumDmnd0.MMItem = TItems.MMItem) "
  sql2 = sql2 & " LEFT JOIN TPlanParam ON (TItems.MMItem = TPlanParam.MMitem   AND TPlanParam.Loc = 'HN01') "
  sql2 = sql2 & "GROUP BY "
  sql2 = sql2 & " SumDmnd0.MMItem, "
  sql2 = sql2 & " IIf(Year([ToDate])*100 + Month([ToDate]) < "
  sql2 = sql2 & "     Year(Date())*100+Month(Date()),"
  sql2 = sql2 & "     Year(Date())*100+Month(Date()),"
  sql2 = sql2 & "     Year([ToDate])*100+Month([ToDate]))"
  sql2 = sql2 & ";"
 
  sql3 = ""
  sql3 = sql3 & "WITH SumDmnd0("
  sql3 = sql3 & " MMItem , "
  sql3 = sql3 & " TransType,"
  sql3 = sql3 & " TransQty,"
  sql3 = sql3 & " DemandNxtlvl,"
  sql3 = sql3 & " ToDate) as (" & sql & ")" & sql2
  rst.Open sql3, DBcn
  'Set rst = DBcn.Execute(sql3)
  While Not rst.EOF
please advise ?
Thank you,
Yalon

I'm still looking for help with this issue.
Thank you

Is this what you ask?
WITH SumDmnd0( MMItem ,  TransType, TransQty, DemandNxtlvl, ToDate) as (SELECT  TDisplay.MMItem,  TDisplay.TransType,  TDisplay.TransQty,  TDisplay.DemandNxtlvl,  TDisplay.ToDate FROM TDisplay WHERE  TDisplay.TransQty < 0  AND TDisplay.ItemBomLvl = 6    AND TDisplay.DemandNxtlvl > 0 ;)SELECT  SumDmnd0.MMItem,  IIf(Year([ToDate])*100+Month([ToDate])<      Year(Date())*100+Month(Date()),     Year(Date())*100+Month(Date()),      Year([ToDate])*100+Month([ToDate])) AS MntDmnd,      Sum(SumDmnd0.DemandNxtlvl) AS DmndNxtlvl,      First(TItems.HanItem) AS HanItem,      First(TItems.MrpCtrl) AS MrpCtrl,      First(TPlanParam.ActiveForPO) AS ActiveForPO FROM (SumDmnd0  INNER JOIN TItems ON SumDmnd0.MMItem = TItems.MMItem)  LEFT JOIN TPlanParam ON (TItems.MMItem = TPlanParam.MMitem AND TPlanParam.Loc = 'HN01') GROUP BY  SumDmnd0.MMItem,  IIf(Year([ToDate])*100 + Month([ToDate]) <      Year(Date())*100+Month(Date()),     Year(Date())*100+Month(Date()),     Year([ToDate])*100+Month([ToDate]));