title: How to write a CTE Recursive Query in VBA script?
body: <p>I have a VBA script written to query HP ALM database using OTA API.</p>
  
  <p>I want to query the database using a Recursive CTE. I don't know how to write that script within this VBA script.</p>
  
  <p>VBA Script:::</p>
  
  <pre><code>Sub Extractor()
  
      Const QCADDRESS = "http://alm/qcbin"
      Const DOMAIN = "DOMAIN"
      Const PROJECT = "PROJECT"
      Const QCUSR = "user.name"
      Const QCPWD = "123456"
  
      Dim QCConnection, com, recset
      Dim XLS, Wkb, Wks, i
  
      Set QCConnection = CreateObject("TDApiOle80.TDConnection")
      QCConnection.InitConnectionEx QCADDRESS
      QCConnection.Login QCUSR, QCPWD
      QCConnection.Connect DOMAIN, PROJECT
  
      Set com = QCConnection.Command
  
      com.CommandText = "Select * from ALL_LISTS"
  
      Set recset = com.Execute
  
      Set XLS = CreateObject("Excel.Application")
      XLS.Visible = False
      Set Wkb = XLS.Workbooks.Add
      Set Wks = Wkb.Worksheets(1)
  
      i = 1
      Wks.Cells(i, 1).Value = "Data"
  
  
      If recset.RecordCount &gt; 0 Then
      i = 2
      recset.First
      Do While Not (recset.EOR)
      Wks.Cells(i, 1).Value = recset.FieldValue(0) 'fieldvalue(0) because the query extract only 1 field.
      i = i + 1
      recset.Next
      Loop
      Wkb.SaveAs "C:\myfile.xls"
      End If
  
  
      Wkb.Close
      XLS.Quit
  
      QCConnection.Disconnect
  
      Set recset = Nothing
      Set com = Nothing
      Set QCConnection = Nothing
      Set XLS = Nothing
      Set Wkb = Nothing
      Set Wks = Nothing
  
  
  End Sub
  </code></pre>
  
  <p>CTE Query::::</p>
  
  <pre><code>with ReqCTE
  as
  (
  SELECT
  RQ_REQ_ID,
  RQ_REQ_NAME,
  RQ_FATHER_ID, 
  0 as lvl
  FROM
  td.REQ
  where
  RQ_REQ_ID = {?Father_ID}
  
  union all
  
  select
  Folders.RQ_REQ_ID,
  Folders.RQ_REQ_NAME, 
  Folders.RQ_FATHER_ID,
  Child.lvl +1
  from
  ReqCTE as Child
  join td.REQ as Folders on Folders.RQ_REQ_ID = Child.RQ_FATHER_ID
  );
  
  select * from ReqCTE;
  </code></pre>