title: SQL Query to VBA Array
body: <p>As the title suggests I am looking into different ways to store the contents of a query into an array. I have been experimenting with different varieties of doing this, but it seems to be that most of these ways are in correct in their output. This is of course do to my lack of understanding of how this is supposed to be appropriately done, so after a while of experimenting I have decided to ask whats the best approach for this? I will share with you some of my approaches thus far and you can see where my investigation has landed me. </p>
  
  <pre><code>Dim MyArray() As Variant
  MyArray = rst.GetRows(rst.RecordCount)
  </code></pre>
  
  <p>This was <em>ok</em> yet this stored all the information vertically instead of horizontally. Is there a way to flip that? Would that be through the use of ReDim? Or is this due to the fact the rows are getting stored in array dimensions and thus they naturally vertical? </p>
  
  <pre><code>        Index = 0
          Do While Not rst.EOF
                  ReDim Preserve MyArray(1, Index)
                  MyArray(0, Index) = CStr(rst.Fields(0).Value)
  
                  'Safety check to make sure the value isn't null (was having problems before)
                  If rst.Fields(1).Value &lt;&gt; vbNullString Then
                      MyArray(1, Index) = CStr(rst.Fields(1).Value)
                  End If
              Index = Index + 1
              rst.MoveNext
          Loop
  
          sheet.Range("a1:ba10000").Value = MyArray
  </code></pre>
  
  <p>This again stored things vertically, but didnt output the records correctly, and in fact only pull the first two columns of info per record, the rest was output as #N/A#. I think I was closer with my original approach, but decided experimenting might land me somewhere. </p>
  
  <p>Do you peeps have some suggestions or can point me in the right direction?</p>