If you’ve ever wondered whether a SQL statement you executed actually modified any records, there is a property of the DAO database object that can help you.

You can get the number of affected records of a DELETE, UPDATE, or INSERT type query immediately after you’ve run it. Use this syntax:

Dim myDb as DAO.Database
Set myDb = CurrentDb

myDb.Execute "DELETE * FROM tmpCustomers"

Debug.Print myDb.AffectedRecords

myDb.Execute "INSERT INTO tmpCustomers VALUES ( 15, 'Bob', 'Smith')"

Debug.Print myDb.AffectedRecords

myDb.Execute "UPDATE tmpCustomers SET FirstName='Robert' WHERE FirstName='Bob'"

Debug.Print myDb.AffectedRecords

This also works if you are using a recordset that is based on the DAO.Database typed object. It will update the value with each query, so you need to make sure you use the property before you do any additional queries.