by Yuening Dai (30 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 12th August 1999
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
A speedy way to find how many rows are in a large table.
VBcoders
'Connection object, using ODBC driver.
'
'Benefits:
'1. If the table is very large, this way will be very fast. In my test,
'a MSAccess table has almost 180K records. Once I have a active Connection
'object, it took about one second to get the count of records. If I use
'ADODB.Recordset to open the table and then use Recordset.RecordCount property
'to do the same job, it took more than 50 seconds.
'2. In case the Recordset dose not support adApproxPosition or adBookmark, like
'my case using ODBC driver for Oracle, you can use this.
'Anyone has better way to do this, please let me know. I'll appreciate your
'generosity.
'
'I think it will work for VBScripts or ASP too.
'
Public Function plGetRecCount(objCnn As ADODB.Connection, sTBL As String) As Long
'
Dim Rst As New ADODB.Recordset
Dim lRecCount As Long
Dim sSQL As String
'
On Error GoTo Err_Handler
'
sSQL = "SELECT Count(*) As RecNum From " & sTBL
'
Set Rst = objCnn.Execute(sSQL)
'
lRecCount = Rst.Fields("RecNum")
plGetRecCount = lRecCount
'
Rst.Close
Set Rst = Nothing
'
Exit Function
'
Err_Handler:
'
plGetRecCount = -1
MsgBox objCnn.Errors(0).Description
'
End Function
