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
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
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