title: VBA "rowset does not support fetching backward" error with .cursertype adjusted
body: <p>Using the following code I open a connection with SQL server and drop the results straight into an array.  My problem is that I get an error for the rowcount, which I need to redim my array.  The error I get is on the line indicated below and reads </p>
  
  <blockquote>
    <p>"rowset does not support fetching backward"</p>
  </blockquote>
  
  <p>All answers I can find suggest the cursor type as the problem, but I have changed this as far as I can tell.  Apologies for the long code, I felt it best to leave the start in.</p>
  
  <pre><code>Function ConnectServer() As String()
  'Working SQL Server connection
  
  Dim conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sConnString As String
  Dim strSqlQuery As String
  Dim iCols As Long
  
  
  ' Create the connection string.
  sConnString = "Provider=SQLOLEDB;Data Source=wait;" &amp; _
                "Initial Catalog=what;" &amp; _
                "User Id=foo;" &amp; _
                "Password=bar;"
  
  ' Create the Connection and Recordset objects.
  Set conn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  rs.CursorType = adOpenDynamic
  rs.CursorLocation = adUseClient
  conn.CommandTimeout = 50
  
  ' Open the connection and execute.
  conn.Open sConnString
  
  '    Debug.Print strSqlQuery
  Set rs = conn.Execute("SELECT DISTINCT a FROM b")
  
  ' Check we have data.
  If Not rs.EOF Then
  '*****************Problem here********************
      rs.MoveLast
      Debug.Print rs.RecordCount
  
  'Read into array and cleanup...
  End If
  
  End Function
  </code></pre>
  
  <p>I don't believe this to be a duplicate of this question:</p>
  
  <p><a href="https://stackoverflow.com/questions/14122308/rowset-does-not-support-scrolling-backward">Rowset does not support scrolling backward</a></p>
  
  <p>Because I already incorporated the answer into my code and the problem still persists.</p>