Database - Why does Recordcount sometimes equal -1?

The RecordCount property returns the number of records that are in the recordset, so it may seem odd that it can return a negative number (as you can't have a negative amount of records!). However, this can happen, and is entirely intentional - what it means is that the number of records is not known at the moment.

This might seem odd and/or annoying, but it can often be a good thing, because getting an accurate RecordCount takes time and memory (in simple terms, all of the records need to be sent from the database to your program), and in most cases you do not actually need to know it at all. For example:

• If you are filling a control (perhaps a ListBox), it is more efficient to check for .EOF instead, as shown in other articles in our FAQs, such as: How can I fill a ComboBox/ListBox with values from a database?
  
  
• If you are showing one record at a time (perhaps in textboxes) along with Next/Previous/First/Last buttons, those can easily be done with .MoveNext etc, and if you want to disable the buttons when apt check whether .BOF or .EOF are true.
  
  To show the user the amount of records, it is actually more efficient to run an extra "SELECT Count(*)" query using the same FROM/WHERE/etc clauses.
  
  
• If you want to put the records into an array, and think that knowing the RecordCount will help for ReDim.. take a look at the .GetRows method of the recordset, as this will automatically size and fill the array for you.

If none of the above apply to your situation, and you really need a RecordCount, there are usually ways to make it work. The main reasons for a value of -1 are the CursorType and CursorLocation you have selected.

If you used connection.Execute to open the recordset, use recordset.Open instead - as .Execute does not allow you to specify the CursorType (it is always adOpenForwardonly).

If the CursorType is adOpenForwardonly (the fastest option) the amount of records cannot be known until you reach the end of them. If it is adOpenDynamic the amount of records can change at any time based on other peoples actions, so a RecordCount is unsafe. Instead, use adOpenKeyset or adOpenStatic (explanations of the different CursorType values can be seen here).

If the CursorLocation is adUseServer, the records are not sent to your program/computer immediately, so the amount is unknown until they have all arrived. Setting it to adUseClient will make it work, however that has extra implications (such as reduced speed and increased memory usage, and may automatically select a different CursorType to the one you chose), so you should think carefully before changing it.

Another option is to do a .MoveLast , which forces all of the records to be loaded... but of course that is not apt if your CursorType is adOpenForwardonly, as you then can't access any records except the last one!

Note that the Provider/Driver you are using can also cause this behaviour (or force a CursorType of adOpenForwardonly), so you may need to use an alternative one.