Record count

[JanTore]

How do i do to get info on how many records there are in a table and write it to a text box or a label

Thanks
newbie

 

[Karl Blessing]

if you are using ADO it's as simple as.

YourRecordset.Recordcount

keep in mind, the cursors I belive must be either static, or forwardonly, otherwise recordcount will return a -1. Karl Blessing aka kb244{fastHACK}

 

[TondoBoy]

Here's a sample if your are making an ADO connection.

Make your connection:
dim conn as adodb.connection
dim rs as adodb.recordset
dim strSQL as string

set conn = new adodb.connection
set rs = new adodb.recordset
strSQL = "SELECT * from tablename"
conn.open = "Microsoft.jet.OLEDB.4.0;data source ="path\dbname.mdb"
rs.open strSQL,conn,adopendynamic,adlockoptimistic
msgbox "Record Count: " & rs.recordcount


There are many ways to skin a cat. This is just one of them. I hope it helps....

 

[Karl Blessing]

um, you cant get a recordcount back from a dynamic cursor as I've stated earlier, must be static, or forward only. Karl Blessing aka kb244{fastHACK}

 

[woyler]

Actually the recordset has to be opened either static or Keyset. The Forward Only cursor returns only -1 as does the dynamic

 

[JanTore]

HI and thanks,

YourRecordset.Recordcount worked fine for me, I haven't tried TondoBoy's solution yet, but I will later.

Thanks again..

 

[JanTore]

Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This will display the current record
lblStatus.Caption = "Record: " & CStr(adoPrimaryRS.AbsolutePosition)
'This displays the total of records
lblStatus1.Caption = "Total:" & CStr(adoPrimaryRS.RecordCount)
End Sub



JanTore

 

[woyler]

You can forget the whole cursor type debate and just go with a modified SQL.
SELECT Count(*) AS RecordCount
FROM Table1;
This will return the number of records in the table.
ie:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lRecordCount As Long
Dim sSQL As String

Set cn = CreateObject("ADODB.Connection&quot
Set rs = CreateObject("ADODB.Recordset&quot

cn.Open "Microsoft.jet.OLEDB.4.0;data source ="path\dbname.mdb"

sSQL = "SELECT Count(*) AS RecordCount FROM TABLENAME"
rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText And adExecuteNoRecords
lRecordCount = rs(0)