title: How do you perform a SQL query on a recordset in VBA?
body: <p>I am using Excel VBA in where I have located in different locations across different sheets which I will need to run some SQL queries on.</p>
  
  <p>So to start with, I have bashed together some 'simple' functions to try for proof of concept so see whether I can get this working.  So far, I have this:</p>
  
  <pre><code>    Private rstADO As ADODB.Recordset
  
  Private Function StartEre()
  
      Call SetupRecordset
      Call Add("123", "ab", "ba")
      Call Add("321", "ba", "ab")
  
      Dim rs As ADODB.Recordset
      Set rs = search("123", rstADO)
  
  End Function
  
  Private Function search(emp As String, oRecordset As ADODB.Recordset) As ADODB.Recordset
  
      Dim strSQL As String
      Dim cn As New ADODB.Connection
      Dim rs As ADODB.Recordset
  
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; ThisWorkbook.FullName &amp; ";Extended Properties=""Excel 8.0;HDR=Yes"";"
  
      strSQL = "SELECT EmployeeID FROM oRecordset"
      Set rs = cn.Execute(strSQL)
  
      Set search = rs
  
  End Function
  
  Private Function Add(emp As String, first As String, last As String)
  
      Dim fieldsArray(2) As Variant
      Dim values(2) As Variant
  
      fieldsArray(0) = "EmployeeID"
      fieldsArray(1) = "FirstName"
      fieldsArray(2) = "LastName"
      values(0) = emp
      values(1) = first
      values(2) = last
  
      Call rstADO.AddNew(fieldsArray, values)
  
  End Function
  
  
  Private Function SetupRecordset()
  
      Dim fld As ADODB.Field
  
      Set rstADO = New ADODB.Recordset
  
      With rstADO
          .Fields.Append "EmployeeID", adInteger, , adFldKeyColumn
          .Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
          .Fields.Append "LastName", adVarChar, 20, adFldMayBeNull
  
          .CursorType = adOpenKeyset
          .CursorLocation = adUseClient
          .LockType = adLockPessimistic
          .Open
      End With
  
  End Function
  </code></pre>
  
  <p>My issue is in the <code>search</code> function at the point where I have my SQL string:</p>
  
  <pre><code>strSQL = "SELECT EmployeeID FROM oRecordset"
  </code></pre>
  
  <p>How do I get the <code>FROM</code> clause to point to the recordset supplied to this function?
  This is only a simple function, but eventually there will be (if I can get this to work) a number of functions with different SQL queries performing different actions.</p>
  
  <p>Edit:
  Having been identified as a possible duplicate from <a href="https://stackoverflow.com/questions/19755396/performing-sql-queries-on-an-excel-table-within-a-workbook-with-vba-macro">Performing SQL queries on an Excel Table within a Workbook with VBA Macro</a> I want to add clarity.</p>
  
  <p>The post mentioned queries a range from a sheet whereas I want to query a recordset which is pre-filled.  My <code>search</code> function has an argument - oRecordset which contains all the data I want to query using SQL.  I do not want to query the sheets directly as in the previously mentioned link.</p>