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
joacim@programmer.net
joacim@yellowblazer.com
www.YellowBlazer.com (http://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.