RecordCount Property not working

Question

This is my code :

Dim conServer As ADODB.Connection
Dim rstResult As ADODB.Recordset
Dim strSQL As String
Set conServer = New ADODB.Connection
conServer.ConnectionString = "PROVIDER=SQLOLEDB; " _
& "DATA SOURCE=192.168.0.204; " _
& "INITIAL CATALOG=REPORTDB2; " _
& "User ID=sa;" _
& "Password="

conServer.Open

Set rstResult = New ADODB.Recordset
strSQL = "set nocount on; "
strSQL = strSQL & "select *  from Table1;"
rstResult.ActiveConnection = conServer

rstResult.Open strSQL

MsgBox rstResult.RecordCount, , rstResult.Fields(1).Value

With rstResult
    .MoveFirst
    Do Until rstResult.EOF
       Debug.Print abc & "," & .Fields(0) & "," & .Fields(1).Value
      .MoveNext
    Loop
End With

The MSGBOX Line showing me -1 as a RecordCount, but the same time rstResult.Fields(1).Value showing the first records value. Even I check through "do until...loop" all the records in msgbox or in debug window are perfectly showing. The problem is it is not displaying or working recordcount property.

Answer 1

You cannot use RecordCount in that way.

It will give you the correct number of records only after you have looped through all records, but not before you have looped through the records.