Easy way to get the record count in a database?

[tpatten]

Does anyone know an easy way to get the total record count of all the tables in an Access database?

[Joacim Andersson]

Sure, here's an easy example using DAO:

Private Function GetRecordCount(sDatabase As String, sTableName As String) As Long
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sDatabase)
Set rs = db.OpenRecordset(sTableName, dbOpenDynaset)
rs.MoveLast
GetRecordCount = rs.RecordCount
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

Good luck!

------------------
Joacim Andersson
[email protected]
[email protected]
www.YellowBlazer.com

[MartinLiss]

I haven't tested it, but this will probably do it.

    Dim nCtr As Integer
    Dim MyDatabase As Database
    Dim lCount As Long
    Dim MyRS As Recordset
    
    Set MyDatabase = Workspaces(0).OpenDatabase("E:\RWT 6.0\data\rwtu.mdb")
    
    For nCtr = 0 To MyDatabase.TableDefs.Count - 1
        If Left(MyDatabase.TableDefs(nCtr).Name, 4) <> "Msys" Then
            Set MyRS = MyDatabase.CreateSnapshot("Select * From " & MyDatabase.TableDefs(nCtr).Name)
            MyRS.MoveLast
            lCount = lCount + MyRS.RecordCount
            MyRS.Close
        End If
    Next

The check for "MSys" excludes the system tables that you are probably not interested in.


------------------
Marty

[tpatten]

Martin - After a few minor edits, your code worked like a charm. Thanks!

Here were the changes:

"Msys" was changed to "MSys"

MyDatabase.CreateSnapshot was changed to Mydatabase.OpenRecordset

Once again - thanks.