The RecordsAffected Property is a Powerful Tool for Defensive Programming

A big part of defensive programming involves moving errors up the error hierarchy.

Syntax errors
Compile errors
Misunderstood requirements (before you start writing code)
Automated test errors
Runtime errors
Misunderstood requirements (after you've written the code)
Logic errors

In other words, writing your code so that instead of logic errors you generate runtime errors. Instead of runtime errors, you generate compile-time errors.

Validating Assumptions with RecordsAffected

Let's say you have a procedure to delete a record from a table. In this hypothetical situation, your business logic assumes that this procedure will always delete one record (and only one record).

Sub DeleteCustomer(CustomerID As Long)
    CurrentDb.Execute "DELETE * FROM Customer " & _
                      "WHERE CustomerID = " & CustomerID, dbFailOnError
End Sub

This seems pretty straightforward. CustomerID is the unique primary key for the Customer table. How could running the above query ever result in deleting anything besides a single row?

Murphy's Law

If anything can go wrong, it will.

I can think of at least three situations just off the top of my head where the above query might not delete a single record.

1. The CustomerID does not exist

This is an obvious one. What if there is no customer with that CustomerID? In that case, running the above query will delete zero records. That might be OK, but I specifically stated that the business logic expects one (and only one) record to be deleted every time the function is called.

2. Cascading deletes

What if there is a cascading delete relationship between the Customer table and the Invoice table? Suddenly, that single DELETE statement could remove lots of records.

Wouldn't you want to know if some unsuspecting developer came along and changed the nature of the relationship between the Customer and the Invoice table? Even (or especially) if that unsuspecting developer is you in three years.

3. Merge replication

What happens if someday you decide to include the Customer table in a merge replication scheme? Maybe you add a DeviceID column to create a compound primary key with CustomerID and DeviceID.

Tasha adds "Dunder Mifflin" as a customer on her laptop with CustomerID = 45 and DeviceID = 1. At the same time, Joselio adds customer "Initech" on his laptop with CustomerID = 45 and DeviceID = 2. The merge replication runs and now there are two customers with CustomerID of 45.

What do you think will happen when someone runs DeleteCustomer 45 to remove "Dunder Mifflin?" It will be bye-bye, "Initech," too.

Logic Errors are the Worst

All three of the above examples are logic errors. And logic errors are insidious because they can live in your code for years before anyone even realizes that they're there.

Runtime Errors are Better than Logic Errors

Why are runtime errors better? Because at least you know there is a problem and you can do something about it. This is especially true if you have set up automated error reporting.

Converting Logic Errors to Runtime Errors

So how can we convert the logic errors above into runtime errors? By testing our assumptions and explicitly raising an error if our assumption is wrong. In this case, we can do that with the Database.RecordsAffected property.

Let's rewrite our procedure to take advantage of this technique:

Sub DeleteCustomer(CustomerID As Long)
    Dim Db As DAO.Database
    Set Db = CurrentDb
    Db.Execute "DELETE * FROM Customer " & _
               "WHERE CustomerID = " & CustomerID, dbFailOnError
    
    If Db.RecordsAffected <> 1 Then 
        'https://nolongerset.com/throwing-errors-in-vba/
        Throw "DeleteCustomer deleted {0} records for CustomerID: {1}", _
              Db.RecordsAffected, CustomerID
    End If
End Sub

What About Debug.Assert?

With Debug.Assert, you can be alerted when some condition is not what you expect while you are developing.

Conditionally suspends execution when booleanexpression returns False at the line on which the method appears.

Assert invocations work only within the development environment. When the module is compiled into an executable, the method calls on the Debug object are omitted.

That's good, but there is no way you can cover every edge case that may develop in the wild while you are developing.

The main advantage of the Assert statement is that it allows you to omit computationally expensive checks from your production code. To take advantage of that in Access, though, you would need to compile your front-end to .mde or .accde. I don't do that, though, so there is zero incentive for me to use the Debug.Assert statement.

If it's a big enough problem that I want to be notified about it while developing, I sure as heck want to be notified about it in production.

What About Unit/Integration Tests?

To quote Donald Rumsfeld:

"...as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns—the ones we don't know we don't know. ...it is the latter category that tends to be the [most difficult]."

The fundamental limitation of automated testing is that it only concerns itself with known-knowns and known-unknowns. By definition, it is impossible to design a test to cover unknown-unknown conditions.

By including code to throw an error at runtime when program conditions violate some assumption we made at design time, we can properly account for this most difficult category of problems: the unknown-unknowns.