General discussion


Number of records in a table with VBA

By darrellrisley ·
How can I determine the number of records in a table programmatically using VBA?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by bschaettle In reply to Number of records in a ta ...

This is crude, bu it works...

Function NumberOfRecords(sTableName as String) as integer
Dim MyRecordset As Recordset
Dim MyQueryDef As QueryDef
Dim MySQL As String
NumberOfRecords = 0
On Error GoTo ErrHandler
Set MyQueryDef = Nothing
Set MyRecordset = Nothing
MySQL = "SELECT * FROM " & sTableName
Set MyQueryDef = _
CurrentDb.CreateQueryDef("", MySQL)
Set MyRecordset = MyQueryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges, dbOptimistic)
NumberOfRecords = MyRecordset.RecordCount
On Error GoTo 0
Set MyRecordset = Nothing
Set MyQueryDef = Nothing
End Function

Related Discussions

Related Forums