title: One RecordSet with multiple Connections & SQL Querys
body: <p>I have a VBA application that calls several sheets and obtains a set of data for each of them. All the information obtained must end in a single variant matrix.</p>
  
  <p>I have thought of several solutions. These are:</p>
  
  <ol>
  <li>The first of them, join the recordsets to get one only. </li>
  <li>The second one would be to sequentially dump each of the RecordSets in
  the single matrix</li>
  </ol>
  
  <p>Both solutions do not seem to be the solution ...
  This is the code for solution number 1:</p>
  
  <pre><code>Sub Test()
  Dim RS01 As ADODB.Recordset
  Dim RS02 As ADODB.Recordset
  Dim Query As String
  Dim FField As Variant
  Dim Pair As Variant
  Dim Pairs As Variant
  Dim MFTE() As Variant
  Dim Temp() As Variant
  Dim Rows As Long
  Dim Row As Long
  Dim Column As Long
  Dim Connection As String
  'Looping throught the pairs
  Pairs() = Array("EURAUD", "EURCAD")
  For Each Pair In Pairs
      Select Case Par
          Case "EURAUD"
              Query = _
                  "SELECT [FE], [HO], [AP], [MAX], [MIN], [CIE], [PAR]" &amp; _
                  "FROM [EURAUD$]" &amp; _
                  "WHERE (FE &gt;=" &amp; Date1 &amp; ") and (FE &lt;=" &amp; Date2 &amp; ")"
              Connection = _
                  "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                  "Data Source=File Number 1;" &amp; _
                  "Extended Properties=Excel 12.0"
              Set RS01 = New ADODB.Recordset
              RS01.Open Query, Connection, adOpenForwardOnly, adLockReadOnly
          Case "EURCAD"
              Query = _
                  "SELECT [FE], [HO], [AP], [MAX], [MIN], [CIE], [PAR]" &amp; _
                  "FROM [EURCAD$]" &amp; _
                  "WHERE (FE &gt;=" &amp; Date1 &amp; ") and (FE &lt;=" &amp; Date2 &amp; ")"
              Connection = _
                  "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                  "Data Source=File Number 2;" &amp; _
                  "Extended Properties=Excel 12.0"
              Set RS02 = New ADODB.Recordset
              RS02.Open Query, Connection, adOpenForwardOnly, adLockOptimistic
      End Select
  Next Pair
  'Joining RS01 &amp; RS02
  RS01.MoveFirst
  Do Until RS01.EOF
      RS02.AddNew
      For FField = 0 To RS01.Fields.Count - 1
          RS02.Fields(FField).Value = RS01.Fields(FField).Value
      Next FField
      RS02.Update
      RS01.MoveNext
  Loop
  'Dumping data into 1st variant Array
  Do Until RS07.EOF
      Temp() = RS07.GetRows
  Loop
  'Transpose data into 2nd variant Array
  Rows = RS07.RecordCount
  ReDim MFTE(Rows, 7) As Variant
  For Row = LBound(Temp, 2) To UBound(Temp, 2)
      For Column = LBound(Temp, 1) To UBound(Temp, 1)
          MFTE(Row, Column) = Temp(Column, Row)
      Next Column
  Next Row
  End Sub
  </code></pre>
  
  <p>With this solution I have some problems:</p>
  
  <ol>
  <li>The final RecordSet has a mix from the 1st and 2nd RecordSets</li>
  <li>The 1st variant array needs to be transposed</li>
  </ol>
  
  <p>So, is there a better solution?</p>