[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1858
  • Last Modified:

RecordCount of ADODB RecordSet returns -1 in Excel VBA

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
rs.MoveFirst
rs.MoveLast
End If

MsgBox rs.RecordCount

End Sub
0
oonon
Asked:
oonon
1 Solution
 
pcelbaCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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