• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2549
  • Last Modified:

Number of records in an ADO resultset (VBA)

Can anyone help me determine a number of records in an ADO resultset? I know it is easy to do in VB, but for some reason in VBA I can't get it (using VBA for excel if that makes a difference). Thanks in advance.
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Normally it would be rs.RecordCount.  

Depending on your cursor type it may show -1 initially, which means you either have to use a new cursor type, or rs.MoveLast then rs.MoveFirst.

afaik there is no difference in how an ADO recordset behaves based on whether you are using VB or VBA.
MorDeRorAuthor Commented:
right, it does show the value of -1, and it does not like movelast command either (I'm guessing it has to do with a specific type of cursor). In any case, it would be very clumsy to have to do

msgbox rs.recordcount

isn't there a better way to get this info?
MorDeRorAuthor Commented:
OK, I think, the problem is solved. see this article for explanation:


The way I understood it, the idea is to request client side processing for the cursor, this way all the information about it is available right away, and rs.recordcount returns a valid value.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the performance hit for client-side processing is minimal, as the method you discovered gives you the rs.RecordCount right away, then go for it.
MorDeRorAuthor Commented:
yes, I am not so concerned about the performance hit on the client side, and I would like to right as little code as possible to get the number of records.
I would do it like this:

Dim intRowCount as integer
rs.open "Select count (SomeTable.SomeColumnName) as MyCount from SomeTable")
intRowCount = rs!MyCount

msgbox "Rowcount = " & intRowCount

MorDeRorAuthor Commented:
thanks. :) but would mean an extra trip to the server just to get the row count. Even though client side processing has it's limitations, I think it is still better than having to make a special call to the server. Especially if you consider that on an active system the resultset may have a different number of rows from what your query returns...
I would assume that you can set the open method and the lock type...

try doing

adoRec.open (SQL),connection,adOpenKeyset adLockPessimistic


MorDeRorAuthor Commented:
that was so much easier. Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now