CurrentDb.RecordsAffected returns 0. Why?

Question

If I use RecordAffected with CurrentDb.Execute, it always returns 0. If I first create an instance of the database object, it works correctly. Why?

Like this:

Dim Db As Database Set Db = CurrentDb Db.Execute "DELETE * FROM [Samples] WHERE Sample=5" If Db.RecordsAffected = 0 Then MsgBox "Error" End If 

Instead:

 CurrentDb.Execute "DELETE * FROM [Samples] WHERE Sample=5" If CurrentDb.RecordsAffected = 0 Then MsgBox "Error" End If 

I am using Access 2007 and the Microsoft Office 12.0 Access database object library.

Answer 1

Every time you use CurrentDB, this is a new instance.

Answer 2

Use With . Change your code to:

 Dim Db As Database Dim recordAffect = Integer Set Db = CurrentDb With Db .Execute "DELETE * FROM [Samples] WHERE Sample=5" recordAffect = .RecordsAffected 'If Db.RecordsAffected = 0 Then If (recordAffect = 0) Then MsgBox "Error" End If End With