Recordcount returns value of -1

Question

I have the below code which retrieves data correctly however always return a value of -1 for rs.RecordCount. Please assist.

    Sub TEST()

        Dim rs As Object
        Set rs = CreateObject("ADODB.Recordset")

        Dim SQLSTR As String, MYVAL As String
        MYVAL = InputBox("Enter Query")
        SQLSTR = " " & MYVAL & ""
        CONNECT_TO_DWHS
        rs.Open SQLSTR, PERSONALDBCONT

        ActiveSheet.Cells(2, 1).CopyFromRecordset rs
        ActiveSheet.Cells(1, 1).Select

            Debug.Print rs.RecordCount

        CLOSE_CONNECTION_TO_SQL


    End Sub

Answer 1

The ADO Recordsets will ever show this, until they've jumped to the end the first time. So just use rs.Movelast, then rs.MoveFirst.

Now it should be OK...

Hope this helps

Answer 2

rs.CursorType = 3 Appears to work