Access VBA check if Query is empty

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.

Access VBA check empty Query 01

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

Access VBA check empty Query 02

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *