This Access tutorial explains how to check if table or Query is empty in Access VBA.
You may also want to read:
Microsoft Access VBA Export all Query to Text File
Access VBA check if Query is empty
In Access VBA, there are SQL related Functions that simulate SQL. Access DCOUNT Function is one to simulate SQL COUNT.
To count the number of records in a Query, we can use * in the first argument of DCOUNT Function.
For example, the below VBA counts the number of records in Query1
DCount("*","Query1")
Example 1 – check if Table is empty
Suppose that we have the below table that contains employee information, there are a total of 4 records.
To check if this table is empty
Public Sub countRecord()
If DCount("*", "Table1") > 0 Then
MsgBox ("not empty")
Else
MsgBox ("empty")
End If
End Sub
Result
You can do the same to check if a Query is empty.
Example 2 – Export Query if Query is not empty
Suppose we have Query1, Query2, Query3 while Query 1 is empty. We want to export all Query to Excel except the empty Query. The exported Excel will save under C:\test\ folder.
Public Sub countRecord()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
For Each qdf In db.QueryDefs
If DCount("*", qdf.Name) > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdf.Name, "C:\test\" & qdf.Name
End If
Next
End Sub