RecordCount of ADODB RecordSet returns -1 in Excel VBA

Posted on 2014-08-11
Last Modified: 2014-08-11
I am using VBA inside Excel to connect to a database. I need to know the number of records return from a query. I am using the RecordCount property of an ADODB Recordset object, but it is returning -1 instead of the actual number. How do I get the number of rows returned in the record? Here is the code I am using:

Sub mysqlTest()

Dim conMySQL As New ADODB.Connection
Set conMySQL = New ADODB.Connection
Dim rs As ADODB.Recordset

conMySQL.Open "XXX", "XXX", "XXX"

Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.Open "SELECT * FROM table", conMySQL

If rs.RecordCount <> 0 Then
End If

MsgBox rs.RecordCount

End Sub
Question by:oonon
    1 Comment
    LVL 41

    Accepted Solution

    Not all data engines support the RecordCount property, MoveLast is also not supported everywhere...

    You have to issue  SELECT COUNT(*) FROM table  to be sure you'll get the result.

    Another option is to issue  rs.MoveNext  until rs.EOF is False. But that could take a long time for huge tables.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now