Access: Determine number of records affected by an Execute command in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, when I run an SQL using:

DoCmd.RunSQL strSQL,-1

How do I get the row count affected by such SQL?

e.g. If the SQL is an Update statement, how many rows were updated? Something like "SQL%RowCount" in Oracle?

Answer: Unfortunately, using the RunSQL command in VBA code does not allow you to retrieve the number of records affected by an SQL statement. However, you could try using the Execute command.

After the Execute command is run, there is a database property called RecordsAffected that returns the number of records affected by the most recent Execute command.

Here is an example:

Sub ExecuteSQL()

Dim db as Database
Dim LSQL as String

Set db = CurrentDb()
LSQL = "Update suppliers set supplier_name = 'IBM'"

db.Execute LSQL

MsgBox CStr(db.RecordsAffected) & " records were affected by this SQL statement."

End Sub

This subroutine performs an update to the suppliers table and then prints out a message box saying how many records were affected.

If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.