oonon
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.