Recordset Recordcount = -1; SQL Statement opens with 2 records in Query

[NeoPa]

MikeTheBike:
Don't know if this helps.

Oh yes. You can be sure of that.

I would suspect that a dynamic cursor should work for any SQL engine that supports it. I would also expect Jet to be firmly within that list, but as you say, the documentation leaves much to be desired :-(

@time2hike
Over to you. This (MTB's post) gives you some explanation for why you may be seeing this behaviour. You may want to share where the data is coming from ([t_AllP_...] tables).

[ADezii]

MikeTheBike hit the nail on the head, and it should have been more obvious to me. You cannot expect a Dynamic Cursor to accurately reflect the number of Records in a Recordset, since this number may vary at any time given the nature of the Dynamic Cursor. Replace adOpenDynamic with either adOpenStatic or adOpenKeyset, and you should achieve the desired results. It is my understanding also that if you use a Client Side Cursor, RecordCount will always be correct (Server Side is the Default).

[ADezii]

Here is another little Trick that you can use using GetRows():

Dim rs As ADODB.Recordset
Dim strSQL As String
Dim varRecCount As Variant

strSQL = "SELECT * FROM [Order Details];"

Set rs = New ADODB.Recordset

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

varRecCount = rs.GetRows()

MsgBox "Record Count: " & FormatNumber((UBound(varRecCount, 2) + 1), 0)

OUTPUT:

2,156

[time2hike]

Thank you all for troublshooting this.