Discussion Locked

Number of records in a table with VBA

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How can I determine the number of records in a table programmatically using VBA?

1 reply

by bschaettle

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
ErrHandler:
On Error GoTo 0
MyRecordset.Close
Set MyRecordset = Nothing
MyQueryDef.Close
Set MyQueryDef = Nothing
End Function

This discussion is closed to new replies.