RecordCount

Question

I have an Access DB connected to an oracle back-end. RecordCount will not return the actual recordcount but -1 even if rst.movelast is executed before. A loop, however, as shown below, will do the job. What isn't recordcount working? Thanks.

Dim rst as ADODB.Recordset

dim counter as integer

Set rst = New ADODB.Recordset

Strsql = "SELECT FIRST_NAME, LAST_NAME FROM PEOPLE"

rst.Open Strsql, con, adOpenDynamic, adLockOptimistic

debug.print rst.recordcount 'this will always return -1 even if rst.movelast is executed before. What am I missing here?

'The following loop will do the job

counter = 0

Do While rst.EOF = False

rst.MoveNext

counter = counter + 1

Loop

debug.print counter 'this swill show the correct record count

Answer 1

some recordsets do not support recordcount. I think the odbc driver may have something to do with it.

If you want a count, why do you not execute a "select count(*) from tablename" query?

If the table is linked, you can also use the DCount function.