Checking if a Table is empty

Checking if a Table is empty

If you want to check whether a table has data in it, then there are two ways of doing this. The simpler, but slower way, is to use a Domain Aggregate function on a field that is guaranteed to have data in:

Function fIsTableEmptySlow() As Boolean
    If DCount("[RequiredFieldName]", "[tblName]") = 0 Then
        fIsTableEmptySlow = True
    Else
        fIsTableEmptySlow = False
    End If
End Function

Unfortunately, this is slow anyway, and as the amount of data in the table increases, the time increases. The other way of checking whether a table has data in is to use a recordset, and checking the recordcount, which will return 0 if there are no records (note that normally we would need to force Access to count the number of records in a recordset by using .MoveLast in order to get an accurate recordcount):

Function fIsTableEmpty() As Boolean
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("tblName", dbOpenTable)
    If rs.RecordCount = 0 Then
        fIsTableEmpty = True
    Else
        fIsTableEmpty = False
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Top

Last modified at 06/06/2006 15:00:30