• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• ADO Count database rows Get total number of rows in a table fast even with millions of rows. Much f

ADO Count database rows Get total number of rows in a table fast even with millions of rows. Much f

ADO Count database rows Get total number of rows in a table fast even with millions of rows. Much faster than using "SELECT COUNT(*) FROM

API Declarations



' Add a reference to Microsoft ActiveX Data Objects Library



Rate ADO Count database rows Get total number of rows in a table fast even with millions of rows. Much f

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

    '  Add a reference to Microsoft ActiveX Data Objects Library
    '  Code uploaded by Troy McDowell [email protected]
    Dim cn As ADODB.Connection
    Dim rsSchema As ADODB.Recordset
    Dim rCriteria As Variant
    Dim strDbPath As String
    Dim strDbTable As String
    Dim strDbPassword As String
    
    strDbPath = "c:\path\to\database.mdb"     ' Path to Database file
    strDbTable = "Table Name"                     ' Table Name
    strDbPassword = "Database Password"               ' Database Password
    
    Set cn = New ADODB.Connection
    
       With cn
          .Provider = "Microsoft.Jet.OLEDB.4.0"
          .Properties("Jet OLEDB:Database Password") = strDbPassword
          .Mode = adModeReadWrite
          .Open strDbPath
       End With
    
    rCriteria = Array(Empty, Empty, Empty, Empty, strDbTable)
    
    Set rsSchema = cn.OpenSchema(adSchemaIndexes, rCriteria)
    
    Debug.Print "Total Rows: " & rsSchema.Fields("CARDINALITY").Value
    
    rsSchema.Close
    Set rsSchema = Nothing
    cn.Close
    Set cn = Nothing

End Sub