VBA "rowset does not support fetching backward" error with .cursertype adjusted
<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>