Dim (Where should it be placed) ?

Question

All variables, type and object, will disappear when they go out of scope. That a fact of access. In fact, you cannot keeep them even if you wanted to (try it and see). All object variables are pointers to objects and that is all they are. You often see things like dim db as database...set db = currentdb()...code... db.close. Well, that actually does nothing, because you didn't open currentdb() - Access did. As for the pointer (db) that will disappear when you leave the SUB because it goes out of scope. The only time you need to be worried about retaining pointers (object variable) and other variables is if they are defined in global procedures. I think I'm right, but will stand (sit, actually) corrected. Anybody ?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Dim (Where should it be placed)
Date: 3 July 1998 22:31

Denis,

Sorry, you aren't quite right or I understood your message incorrectly. I think that currentdb() and codedb() create database object descriptors not only object refernces and open databases (there is a  special note in MS Access 97 online help about currentdb() ). Run this code:

Dim mcol As New Collection

Sub x_test()
    On Error GoTo x_test_err
    Dim dbs As Database
    Dim i As Integer
    
    For i = 1 To 1000
        Set dbs = CurrentDb()
        'Set dbs = DBEngine(0)(0)
        mcol.Add dbs
    Next
    Debug.Print "mcol.count = " & mcol.Count
x_test_exit:
    Set mcol = Nothing
    Exit Sub
x_test_err:
    Debug.Print "Err = " & Err.Number & " - " & Err.Description
    Debug.Print "i = " & i & ", dbengine(0).databases.count = " & DBEngine(0).Databases.Count
    Resume x_test_exit
End Sub

Then comment set dbs=currentdb() and uncomment set dbs=dbengine(0)(0) and run it again.

If you set private object variables on module level and don't set them to Nothing on sub exit they continue to "eat" MS Access memory resources - so if your main goal is to write stable apps without annoying and "miracolous" side-effect - always "recycle" object variables memory setting them to nothing when you leave sub/module/application even if they are local (see and run the sample code in P.S.)

All and any additional info and comments on "object variables behaviour" subject are very wellcome,

Best wishes,
Shamil

P.S. The code:

Private db1 As Database

Sub a_test()
    DbsList "OnStart"
    b_test
    DbsList "OnEnd"
End Sub

Sub b_test()
    'Dim db1 As Database
    Dim db2 As Database
    Dim db3 As Database
    Dim db4 As Database
    
    Set db1 = DBSet(1)
    DbsList "set db1=CodeDb() used. db1 is dbengine(0)(0) = " & (db1 Is DBEngine(0)(0))
    
    Set db2 = DBSet(2)
    DbsList "set db2=Currentdb() used. db2 is dbengine(0)(0) = " & (db2 Is DBEngine(0)(0))
    
    Set db3 = DBSet(3)
    DbsList "set db3=dbengine(0).opendatabase(currentdb().name) used." & _
            " db3 is dbengine(0)(0) = " & (db3 Is DBEngine(0)(0))
    
    Set db4 = DBSet(4)
    DbsList "set db3=dbengine(0)(0) used. db4 is dbengine(0)(0) = " & (db4 Is DBEngine(0)(0))
    
End Sub

Function DBSet(ByVal vbytOption As Byte) As Database
    Select Case vbytOption
    Case 1: Set DBSet = CodeDb()
    Case 2: Set DBSet = CurrentDb()
    Case 3: Set DBSet = DBEngine(0).OpenDatabase(CurrentDb().Name)
    Case 4: Set DBSet = DBEngine(0)(0)
    Case Else
            Set DBSet = Nothing
    End Select
End Function

Function DbsList(ByVal vstrMsg As String)
    Dim intCount As Integer
    Dim dbs As Database
    
    DebugPrint
    DebugPrint "DbEngine(0).Databases listing - " & vstrMsg
    DebugPrint
    For Each dbs In DBEngine(0).Databases
        intCount = intCount + 1
        DebugPrint intCount & ". " & dbs.Name
    Next
End Function

Function DebugPrint(Optional ByVal vstrMsg As String = "")
    Debug.Print vstrMsg
End Function