• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• A speedy way to find how many rows are in a large table.

A speedy way to find how many rows are in a large table.

A speedy way to find how many rows are in a large table.

Rate A speedy way to find how many rows are in a large table.

• 1
• 2
• 3
• 4
• 5

(3(3 Vote))

'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