title: How to work with multiple recordsets out of one procedure from SQL Server in vba
body: <p>I have linked a MS Access frontend to SQL Server (and somehow regret that decision). Now I have a bunch of procedures with a complex but similar <code>WHERE</code> part but different <code>SELECT DISTINCT</code> so I would like to combine them to a single procedure like</p>
  
  <pre><code>SELECT * FROM myTable1 INNER JOIN @tblFromFunc()
  SELECT * FROM myTable2 INNER JOIN @tblFromFunc()
  </code></pre>
  
  <p>and a predefined table-valued function <code>@tblFromFunc</code> which will handle my <code>WHERE</code> just once. Hope for better performance and to make maintenance easier.</p>
  
  <p>That works fine on the SQL Server an I even can get the first of this two independent results (just tried two of them) visible in MS Access by using <code>DAO.QueryDef</code> and <code>DAO.Recordset</code> in vba.</p>
  
  <p>I found this description of <code>.NextRecordset</code> <a href="https://msdn.microsoft.com/en-us/library/office/ff193483.aspx" rel="nofollow noreferrer">here</a>, in short as vba:</p>
  
  <pre class="lang-vb prettyprint-override"><code>DIM rst as DAO.Recordset
  SET rst = functionConnectServer("NameOfSp")
  booNext = True 
  intCount = 1 
  With rst 
  Do While booNext  
      Do While Not .EOF 
          Debug.Print , .Fields(0), .Fields(1) 
          .MoveNext 
      Loop 
      booNext = .NextRecordset  
      intCount = intCount + 1 
  Loop 
  End With 
  </code></pre>
  
  <p>but if I used it in Acceess 2010, I got the response, that somehow <code>.NextRecordset</code>is not supported anymore. So I cannot move to the second recordset, to be honest, I am even not sure, if that second recordset reaches my frontend.</p>
  
  <p>Any hint would make me happy and I am even open to total different strategies for this problem.   </p>