ADO Recordcount doesnt work

[vivek r]

when i run a query using ADO i dont get the recordcount.it
always returns -1

help

[Lothar Haensler]

according to MSDN "this behavior is by design".
the solution:
"Use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType you select. "

MSDN article Q194973

[mihalidis]

hi there,

a fairly efficient method to get the recordcount.
This method is not restricted by cursor- and locktype..



public Function RecordCount(TableName as string) as Long
on error GoTo ErrorHandler
set Connection = new ADODB.Connection
Dim ConnectionString as string
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= MyDb.mdb;"
Connection.ConnectionString = ConnectionString
Connection.Open


'tablename is empty, no table to retrieve.
If len(TableName) = 0 then Exit Function

Dim rs as new ADODB.Recordset
Dim Query as string
Query = "select count(*) as RecCount from " & TableName
rs.Open Query, Connection

If rs.EOF = false And rs.BOF = false then RecordCount = rs!RecCount.Value
set rs = nothing
Exit Function
ErrorHandler:
MsgBox Err.Number & ":" & Err.Description, vbExclamation, "error retrieving RecordCount for table """ & TableName & """"
End Function




good luck