I use RecordsAffected to find how many records added or updated into table,
but it alwasy gives me zero. When I open the table, it shows me updated or
added records. Can anyone tell me why RecordsAffected not returns expected
results? Or any other way can I find how many records affected?
If you are using "CurrentDb" instead of declaring a database variable, you will
not be able to retrieve the RecordsAffected property because each call to
CurrentDb will return a new database object - the database object used to run
the sql will have already been extinguished. So, use this approach (the
following is "air code" and may contain typos or even a syntax error or two
<g>):
'****EXAMPLE START
'Error Handler
On Error GoTo MyErrorHandler
'Declare database object and assign db reference
Dim MyDb As DAO.Database
Set MyDb = CurrentDb()
'Execute your sql statement (action query, only, of course)
MyDb.Execute strSQL, dbFailOnError
'Now, retrieve the RecordsAffected property
MsgBox MyDb.RecordsAffected & " records were added/updated."
MyErrorHandler_Exit:
'Clean up
On Error resume next
Set MyDb = Nothing
'Exit Sub or Function, as appropriate
MyErrorHandler_Err:
MsgBox Err.Description
Resume MyErrorHandler_Exit
'****EXAMPLE END
