How to check if the table is empty in Access 2003?-CodePudding

I need only empty tables in access database. Additionally, it would be great if I can get empty tables from list of tables that I have (part of all tables). But listing all empty tables would work also.

CodePudding user response：

You can use a small VBA function that checks this. Something like:

Function fIsTableEmpty(strTableName As String) As Boolean
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT COUNT(*) FROM [" & strTableName & "];"
    Set rsData = db.OpenRecordset(strSQL)
    fIsTableEmpty = True ' start by assuming that there are records
    If Not (rsData.BOF And rsData.EOF) Then
        If rsData(0) > 0 Then fIsTableEmpty = False
    End If
fExit:
    On Error Resume Next
    rsData.Close
    Set rsData = Nothing
    Set db = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fIsTableEmpty", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

CodePudding user response：

You can use DCount:

Public Function ListEmptyTables()

    Dim Table   As DAO.TableDef
    
    For Each Table In CurrentDb.TableDefs
        If Table.SourceTableName = "" Then
            If DCount("*", Table.Name) = 0 Then
                Debug.Print Table.Name
            End If
        End If
    Next

End Function