title: Query References Another - Access to SQL Server
body: <p>Good morning,</p>
  
  <p>I just received a new assignment and I am struggling with finding an appropriate solution. I have searched through the SO Forums, and through Google, but have not found a workable solution.  Below is my scenario:</p>
  
  <p>We are working out of Microsoft Access to connect to an SQL Server Database via an ODBC Connection.</p>
  
  <p>I wasgiven an incredibly large pass-through SQL query, larger than is able to be processed in MS Access. In this pass-through query, there is a subquery in a WITH...AS method.</p>
  
  <p>I am hoping to be able to split this one, singularly large, SQL pass through query into two: Query One (the subquery), and Query Two (which references the results of the subquery)</p>
  
  <p>I know that by using general Access queries, I can write a Macro like follows...</p>
  
  <pre><code>Sub myQuery()
  ' Edited from http://www.dbforums.com/showthread.php?1667831-Run-multiple-queries-in-sequence-on-click
  
  ' On Error GoTo ErrHandler
  
  ' Run the first query
  MsgBox "Starting first query"
  DoCmd.OpenQuery "first_Query"
  DoEvents
  
  ' Run the second query
  MsgBox "Done. Now starting second query"
  DoCmd.OpenQuery "second_Query"
  DoEvents
  
  MsgBox "Done!"
  End Sub
  </code></pre>
  
  <p>However, these need to be pass-through queries. I believe that the enormously large SQL String is created via a number of user inputs. Regardless, I don't have the ability to change the pass-through SQL that I was given.</p>
  
  <p>Is there anyway I can write a macro that calls the first pass-through query, and then calls the second pass-through query that REFERENCES the result of the first?</p>
  
  <p>Here is an example with what I am working with...</p>
  
  <pre><code>WITH queryOne AS
      (
          SELECT fooID
          FROM tblFoo
          WHERE foodate &gt; ...
      )
  
  SELECT foo, fooone, footwo, foothree
  FROM tblOtherFoo
  WHERE fooID = OtherFooID
  </code></pre>
  
  <p>However, the query is 50000+ characters, exceeding that ~37k limit.</p>
  
  <p>Please feel free to ask any questions. I am stumped by this and would appreciate any feedback or alternative resources.</p>
  
  <p>Thank you!</p>