totn Access

MS Access 2003: Determine number of records affected by an Execute command

This MSAccess tutorial explains how to determine the number of records affected by an Execute command in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, when I run a 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 a 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.

Microsoft Access

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.