rs.RecordCount (ADO)

[elziko]

When I use the RecordCount method I get -1 returned. According to VB Help its because ADO cannot determine the number of records. OK, but can anyone tell me why this might happen so I can correct it and get a record count?

Many Thanks

elziko

 

[sunaj]

Hi,

It depends on the cursortype, see e.g.

http://www.vb-faq.com/Articles/Greener/reccount.asp

Sunaj

 

[sodakotahusker]

You have a forward only cursor. In that situation you have to walk the recordset to get the recordcount.
Change your cursortype property to adOpenStatic. You may need to change locking to adLockBatchOptimistic.

recordset.cursorType = adOpenStatic
recordset.lockType = adLockBatchOptimistic

Set these BEFORE you open the record set.

 

[elziko]

Thanks, I have read the FAQ and now understand why I need to use a static cursor type:

Rec.CursorType = adOpenStatic
Rec.LockType = adLockBatchOptimistic
Rec.Open "Select TESTID from summary", ConStr
Debug.Print Rec.RecordCount

However I still get -1! Anything to do with the fact that its an oracle database?? I wouldn't have thought but its the only thing I can think of.

elziko

 

[drost]

Elziko,

Yep, an Oracle DB is different, you also have to set your Rec.CursorLocation = adUseClient

This should work better for you, your recordset should return correctly.

drost

 

[elziko]

Oh thanks but:

Rec.CursorLocation = adUseClient

solver my problem.

elziko

 

[elziko]

thanks drost... we seem to have posted at the same time!

 

[Codfish]

A return value of -1 often means the cursor does not support the requested operation. Use the RecordSet.Supports method to find out if a particular recordset will support the function you are requesting. Oh and by the way, just because you open a recordset as a particular type, ADO can switch it down to another type with no warning... a type that may not support the operation you are looking for! Thanks MiscroSoft. Lost a few hairs on that one.

Even if a particular recordset type supports an operation, the OLEDB Provider or ODBC driver must also support that operation. Good luck finding out what operations are supported by which interface. trial and error.