In
Roger Carlson said:
The reason I consider it a Best Practice is not that using CurrentDb
in the way you did below is wrong or unclear. I believe it is Best
Practice because it keeps you from getting into the sort of trouble
the Original Poster had.
For instance, the code you posted (slightly modified):
With CurrentDb
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With
does work. However, this will not:
CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & CurrentDb.RecordsAffected
End If
.... reflecting a failure to understand how the CurrentDb method works.
the WITH in your code keeps the database instance open, which makes
it work.
Exactly. The With statement ensures that I am referring to the same
object throughout the block.
In the second, a new database instance is created for each
line. The problem is that the two styles above can most often be
used interchangably, which can lead to the problems the OP had.
However, by creating a database variable,
Dim db As DAO.Database
Set db = CurrentDb
strSQL = "UPDATE ZipAddress SET ZipAddress.State = 'MI';"
With db
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With
Set db = Nothing
this problem is avoided and in fact both coding styles work
identically. It only took 3 extra lines of code to make certain you
avoid nasty surprises like this.
From my point of view, understanding and using the With statement does
the exact same thing in a simpler way. If anything, I would encourage
using the With statement over defining a separate object variable,
wherever that is feasible. Of course, there are cases where multiple
objects are being manipulated in the same block of code, with no special
reason to assign one primacy over the other. Then then I do find it
clearer to use a separate object variable for each object.
We may have to agree to disagree on this point.