Why -1? (1 Viewer)

[AccessKid]

Can anybody please tell me why the RecordCount property returns a -1 in th last line of this code? There are two records in the 'CA' table and I'm using Access 2002.


strCnn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name


Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Open CA table.
Set rstCA = New ADODB.Recordset
rstCA.CursorType = adOpenDynamic
rstCA.LockType = adLockPessimistic
rstCA.Open "CA", cnn1, , , adCmdTable
rstCA.MoveLast
rstCA.MoveFirst
intTotalRec = rstCA.RecordCount

Thanks.

 

[ghudson]

Can you try this...

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CA")
'Debug.Print rst.RecordCount
intTotalRec = rst.RecordCount
rst.Close
Set dbs = Nothing

HTH

 

[AccessKid]

Hi:

I tried your code and I got the correct count. Just wondering what's wrong with the original code using ADO instead of DAO in your code. Anyway, yours is a good alternative if ever there is no solution using the ADO way.

Thanks.

 

[Howlsta]

Your original code should work if you add an extra line to specify the cursor type. The default cursor type is acForwardOnly, you have to set it to openKeyset or OpenStatic.

Look at CursorType in help and RecordCount (tells you why -1 is sometimes returned)

This line should do the trick

rstCA.CursorType = adOpenKeyset

HTH

Rich