Cannot open recordset

[Nova]

This is my code

Dim Cnx As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrCnx As String
Dim CmdRst As String
Dim N As Integer

CmdRst = "SELECT * FROM MatStock WHERE MatID='" & MatIDSel & "';"

Set Cnx = CurrentProject.Connection
Set Rst = New ADODB.Recordset

Rst.Open CmdRst, Cnx, adLockReadOnly, -1
N = Rst.RecordCount
Msgbox N
It works, I can get N
After that I change code from adlockreadonly to adLockPessimistic,-1
but the msgbox show me -1
I don't understand (I change code because I want to update some field in
recordset
How can I correct it?

 

[Tom van Stiphout]

On Mon, 4 Jan 2010 21:59:02 -0800, Nova

For performance reasons the RecordCount is not populated until all
rows are read, and Access (ADO) will lazily load the records. You can
force it by doing:
if not rst.eof then rst.movelast

Of course if all you wanted is a count, you can use a different query:
select count(*) from ... etc.

-Tom.
Microsoft Access MVP

 

[Nova]

Thanks it works

"Tom van Stiphout" เขียน: