CurrentDb.RecordsAffected returns 0. Why?

Question

If I use RecordsAffected with CurrentDb.Execute, it always returns 0. If I first make a instance of a Database object it works properly. 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 of:

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

I'm using Access 2007 and the Microsoft Office 12.0 Access database engine Objects Library.

Aulos

Answer 1

Each time you use CurrentDB, it is a new instance.

Copley

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

Linger