title: How do I retrieve a column of data as an array using vba - from an SSMS database
body: <p>I am looking to return a a column of data to be used as a combo box within excel. To do this I am using a recordset to return my data from a stored procedure. The problem I am having is that I can't figure out how to populate or even display the information I am returning - or even if I am returning all the data.</p>
  
  <p>I know there are solutions online that recommend using "rs.GetRows" to populate an array, however I have had no luck with this. I had originally intended to populate an array by indexing through the records in the recordset and adding them individually. The problem with this is I can never get the size of the recordset and because I am using dynamic SQL the size of my array is never the same. I have been at this for a while and I was wondering if there is a good way to do this.</p>
  
  <p><strong>VBA Code</strong></p>
  
  <pre><code>Public Sub SQL_SP_Column_Data2(ByVal sql_col As String, _
                                 ByVal sql_table As String)
  
      On Error GoTo RunTimeError
  
      Dim array1() As Variant
  
      Dim sqlconxn As ADODB.connection
      Dim sqlcmd As ADODB.Command ' Operates as a command between SQL and vba
      Dim sqlrs As ADODB.recordSet
  
      Dim conxnString As String 
  
      Application.ScreenUpdating = False
  
      ' String used to establish a connection to the database
      conxnString = "driver={SQL Server};" &amp; _
                    "server='server_name';" &amp; _
                    "uid='username';" &amp; _
                    "pwd='password';" &amp; _
                    "database='database_name';"
  
      Set sqlconxn = New ADODB.connection
      Set sqlcmd = New ADODB.Command
      Set sqlrs = New ADODB.recordSet
  
      sqlconxn.ConnectionTimeout = 30
      sqlconxn.Open conxnString ' makes the connection between SQL
  
      MsgBox "Connection 1 state: " &amp; GetState2(sqlconxn.state) 
  
      sqlcmd.CommandType = adCmdStoredProc ' sets command to a stored procedure
      sqlcmd.CommandText = "GET_COLUMN_DATA" ' name of the stored procedure
      sqlcmd.ActiveConnection = sqlconxn ' makes the sql connection
  
      sqlcmd.Parameters.Append _
          sqlcmd.CreateParameter("@column_name", adVarChar, adParamInput, 255, sql_col)
      sqlcmd.Parameters.Append _
          sqlcmd.CreateParameter("@data_table_name", adVarChar, adParamInput, 255, sql_table)
  
      sqlrs.Open sqlcmd
  
      If sqlrs.EOF Then
  
          array1() = sqlrs.GetRows
          Debug.Print "VALUES: " + array1(i)
          sqlrs.MoveNext
          i = i + 1
  
      End If
  
  RunTimeError:     ' Reportd any errors that might occur in the system and
  
      Dim strError As String
      strError = "ERROR: " &amp; Err.Number &amp; vbCrLf &amp; Err.Description
      MsgBox strError
      Debug.Print strError
  
  Exit Sub
  
  End Sub
  </code></pre>
  
  <p>I am using GetRows above because it was the most recommended method, but I can't get it to work the way I am intending it to. In this example I usually get ERROR: 0 or subscript out of range.</p>
  
  <p><strong>SQL Stored Procedure</strong></p>
  
  <pre><code>USE AFCD
  GO
  
  ALTER PROCEDURE dbo.GET_COLUMN_DATA (@column_name nvarchar(max),
                       @data_table_name nvarchar(max)) AS
  
  BEGIN
  
      DECLARE @query nvarchar(max)
  
      SET @query = 'SELECT ' + @column_name + 
               ' FROM ' + @data_table_name + ''
  
      EXEC sp_executesql @query
  
  END
  </code></pre>
  
  <p>When I test this stored procedure I actually get the data that I want to I know my stored procedure is returning what I want, but I don't know how to get that within vba.</p>
  
  <p>Example of expected outputs: 
  If the inputs into the the vba sub are - "WIRE_TYPE", "WIRE_INDEX"</p>
  
  <pre><code>SQL_SP_Column_Data2 "WIRE_TYPE", "WIRE_INDEX"
  </code></pre>
  
  <p>Then the output should look like this:</p>
  
  <pre><code>Stainless Steel
  Steel ER70S-3
  Steel ER70S-6
  Titanium(1)
  Titanium(2)
  </code></pre>
  
  <p><strong>SOLUTION</strong> - This part of code refers to my vba and is right after I execute my SQL SP. Credit to Tim Williams for the answer.</p>
  
  <pre><code> If Not sqlrs.EOF Then
  
          array1() = sqlrs.GetRows()
  
      End If
  
      Dim i As Integer
      For i = 0 To 4
  
          Debug.Print "VALUES: " + array1(0, i) ' 2D array!!!
  
      Next i
  </code></pre>