append query how many records where affected

Of course you can do anything you want with X ... display it in a Text Box, and so on.

You will also want to add some error trapping in case an error occurs with the Execute operation ... since you have dbFailOnError (good)

Here is a full example more or lessv


Public Function mFx()
On Error Goto mFx_Error:

Dim x as Long  ' optional
With CurrentDb
      .Execute "YourActionQueryName", dbFailOnError '
       ' x = .RecordsAffected   'returns the number of records processed by the action query ' optional
End With

mFx_Exit:
   Err.Clear
   ' other clean up here
   Exit Function
mFx_Error:

   ' error trap code here
   GoTo mFx_Exit

End Function

Don't forget the Dots ....

I prefer a different database object.  It is a bit faster -- less overhead.

DbEngine(0)(0).Execute "AppendToTable1", dbFailOnError
debug.print DbEngine(0)(0).RecordsAffected

Select allOpen in new window

Good luck measuring the speed difference with today's faster systems :-)
In theory, yes a tad faster.
I stayed with CurrentDB because that's what the OP was familiar with ... and the real question was how to use RecordsAffected ...

ty