title: Adding ADODB RecordSet item to Collection
body: <p>I'm new to VBA, trying to write an Outlook macro around an Excel spreadsheet via Microsoft's ACE OLEDB Provider. I'm looping through the <code>ResultSet</code> after a query and storing the results in a <code>Collection</code>, along the lines of the following:</p>
  
  <pre><code>Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = &amp;H1
  
  Sub Test()
     Dim conn As New ADODB.Connection
     Dim results As New ADODB.Recordset
     Dim values As Object
     Set values = CreateObject("System.Collections.ArrayList")
  
     conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\me\test.xlsx;" &amp; _
       "Extended Properties=""Excel 12.0; HDR=YES;"""
  
     results.Open "SELECT * FROM [Sheet1$]", _
       conn, adOpenStatic, adLockOptimistic, adCmdText
     Do Until results.EOF
       values.Add results.Fields.Item("num")
       Debug.Print Join(values.toArray, ", ")
       results.MoveNext
     Loop
  End Sub
  </code></pre>
  
  <p>With the following <code>test.xlsx</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/U6aaD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/U6aaD.png" alt="Test Excel data"></a></p>
  
  <p>We get:</p>
  
  <pre><code>1
  2, 2
  3, 3, 3
  4, 4, 4, 4
  5, 5, 5, 5, 5
  </code></pre>
  
  <p>This seems consistent with <code>results.Fields.Item("column_name")</code> returning a reference that is overwritten at each <code>MoveNext</code>, and the reference being what's stored in <code>values</code>.</p>
  
  <p>Not being very familiar with the reference/value mechanics in VBA, is this guess on the right track, and is there some way I can store the values instead?</p>