0

I often see code examples, that go through the laborious and possibly confusing process, of declaring, setting, calling, and any associated cleanup, of Object Variables, that otherwise, work fine without the variable - least of all, on a variable that is Private to that function.

Is this at all really necessary, when writing out the full reference, does just as well?

I've heard arguments, that the code is easier to read, and runs faster. The former is highly subjective, and the latter, I have yet to really notice.

Examples;

Public Sub TransactionExample()

    Dim wrkSpaceVar As DAO.Workspace
    Dim dbVar As DAO.Database

    Set wrkSpaceVar = DBEngine(0)
    Set dbVar = CurrentDb

    On Error GoTo trans_Err
    wrkSpaceVar.BeginTrans

    dbVar.Execute "SomeActionQuery", dbFailOnError
    dbVar.Execute "SomeOtherActionQuery", dbFailOnError

    wrkSpaceVar.CommitTrans

    trans_Exit:
    wrkSpaceVar.Close
    Set dbVar = Nothing
    Set wrkSpaceVar = Nothing

Exit Sub

trans_Err:
    wrkSpaceVar.Rollback
    MsgBox "Transaction failed. Error: " & Err.Description
    Resume trans_Exit
End Sub

vs

Public Sub TransactionExample()

    On Error GoTo trans_Err
    DAO.DBEngine.BeginTrans

    CurrentDb.Execute "SomeActionQuery", dbFailOnError
    CurrentDb.Execute "SomeOtherActionQuery", dbFailOnError

    DAO.DBEngine.CommitTrans

Exit Sub

trans_Err:
    DAO.DBEngine.Rollback
    MsgBox "Transaction failed. Error: " & Err.Description
End Sub

I am not asking about setting variables to "Nothing"; I am asking if they are necessary at all. And for what it's worth, necessary, within the scope, of the examples provided.

Cœur
  • 37,241
  • 25
  • 195
  • 267
tahwos
  • 574
  • 7
  • 21
  • Yes, there are times when it really is necessary, particularly with respect to the `CurrentDb` function. See the related question in the comment below. – Gord Thompson Dec 17 '16 at 20:03
  • Possible duplicate of [Is there any important difference between set dbs=currentdb() and using currentDB() directly?](http://stackoverflow.com/q/21416876/2144390) – Gord Thompson Dec 17 '16 at 20:04
  • The linked question/answer doesn't go into any more detail, than you do here, and to honest - is opinion based, with no supporting examples. – tahwos Dec 17 '16 at 20:34
  • 1
    My responses are not so much opinion-based as they are experience-based. I don't have any concrete examples because I stopped doing "the wrong thing" years ago, so I haven't wasted any time lately tracking down a case where it does fail. – Gord Thompson Dec 18 '16 at 01:48
  • No offense, but given the nature of SO, it's kind of expected, that answers be provided with the same completeness, as questions. I stopped painting cars years ago, and know that it's bad practice to mix systems. But I'm not going to tell someone, not to do it, unless I can back it up with a confident, if not citable; "why" it's a bad idea. – tahwos Dec 18 '16 at 05:52

1 Answers1

2

In short, no - it isn't necessary to store them in local variables because the references will be the same in both of your code samples. The reason why you would set them to local variables is to avoid necessary object dereferencing calls. In your example, DAO.DBEngine is called three times. Each one is essentially a function call that carries some processing overhead to retrieve the object reference you're working with. In your top example, that function call is only made once and the result is cached in the local variable reference.

If you don't want to declare a local variable, you can do the same thing by wrapping code that uses the same reference in a With block:

Public Sub TransactionExample()
    With DBEngine(0)
        On Error GoTo trans_Err
        .BeginTrans
        With CurrentDb
            .Execute "SomeActionQuery", dbFailOnError
            .Execute "SomeOtherActionQuery", dbFailOnError
        End With
        .CommitTrans
trans_Exit:
        .Close
        Exit Sub

trans_Err:
        .Rollback
        MsgBox "Transaction failed. Error: " & Err.Description
        Resume trans_Exit
    End With
End Sub

Unless you're doing a ton of work with it (i.e. looping through it extensively), the performance difference is negligible whichever method you use.

Note - setting variables to Nothing is not necessary. The runtime takes care of that when they leave scope.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Sorry, no. `CurrentDb` is a *function* that returns a copy of the Database object, and there *are* cases where it will not behave the same as a proper object reference. Also, setting object variables to `Nothing` *shouldn't* be required, but long-time VBA/VB6 coders will tell you that some fairly obscure application failures can be caused by failing to do so. – Gord Thompson Dec 17 '16 at 20:16
  • 1
    @GordThompson - If you'll note in the code above, it's only called *once* when the `With` block evaluates. – Comintern Dec 17 '16 at 20:17
  • So your one example works (I assume), but there are lots of other cases where it won't. (Also, nested `With` blocks are not a practice to be encouraged because they can be ambiguous.) – Gord Thompson Dec 17 '16 at 20:19
  • 1
    @GordThompson - Nested with blocks are a matter of taste rather than anything else. The indentation makes it perfectly clear which object is being referenced. There is no functional difference between a `With` block and a local variable - the runtime simply manages it internally, and yes, sets it to `Nothing` at the `End With` statement. – Comintern Dec 17 '16 at 20:21
  • @Comintern Nothing extensive - just a group of action queries, to replace the current embedded macros, with proper `transactions`, and `error trapping`. Instead of users disabling warnings, like they do now... "You are about to...". I didn't make it, I'm just fixing it. If I have to declare on behalf of the `Method`, I will, but it hasn't broke on me yet. – tahwos Dec 17 '16 at 20:54
  • I will add, that these transactions only occur, in the presence of a single user. At any given time there will never be two people, modifying the same records. – tahwos Dec 17 '16 at 21:51