CODEWITHSUNDEEP
vbavariablesobjectms-access
tahwos
tahwos

Reputation: 574

MS Access VBA: Are Object Variables necessary, when they aren't used, beyond the scope in which they are declared?

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.

Upvotes: 0

Views: 215

Answers (1)

Comintern
Comintern

Reputation: 22185

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.

Upvotes: 2

Related Questions