Close all objects (1 Viewer)

[Kayleigh]

Hi
I'm wondering if anyone has code that can close open objects in particular emails. I think these are still knocking about in my database and causing issues.
I have code to close all recordsets when DB closes so looking for something to that effect.
Like this:

Public Function CloseAllRecordsets() As Integer
Dim wsCurr As Workspace
Dim dbCurr As Database
Dim dbWrite As Database
Set dbWrite = CurrentDb
Dim str As String
Dim rs As Recordset
'Dim frm As Form
'For Each frm In Application.Forms
'  If frm.Name <> "frmLogin" Then
'    DoCmd.Close acForm, frm.Name
'  End If
'Next
For Each wsCurr In Workspaces
    For Each dbCurr In wsCurr.Databases
        For Each rs In dbCurr.Recordsets
            str = "INSERT INTO [OpenRecordSets] ( [RSname], [RsCount], [Date] ) SELECT '" & rs.Name & "'," & rs.RecordCount & ",#" & Date & "#"
            dbWrite.Execute str
            MsgBox "Recordset " & vbCrLf & rs.Name & vbCrLf & rs.RecordCount & " record(s) was left open - now closing it.", vbCritical, "Validation"
            rs.Close
            Set rs = Nothing
        Next
        dbCurr.Close
        Set dbCurr = Nothing
    Next
    wsCurr.Close
    Set wsCurr = Nothing
Next
End Function

 

[Eugene-LS]

Closing of all open forms except one :

Spoiler: Closing of all open forms except one :

Public Sub CloseAllOpenForms(Optional sExceptFormName$ = "")
'Closing of all open forms except the one specified in the argument: sExceptFormName
'----------------------------------------------------------------------------------------------
Dim iVal%, iValX%, sVal$
Dim frm As Form
'----------------------------------------------------------------------------------------------
On Error GoTo CloseAllOpenForms_Err
    iValX = Forms.Count
    For iVal = 0 To iValX - 1
        Set frm = Forms(iVal)
        sVal = frm.Name
        'Debug.Print sVal
        If sVal <> sExceptFormName Then
            'If the form is in the add mode - Undo!
            If frm.NewRecord = True Then
                frm.Undo
            End If
            DoCmd.Close acForm, sVal, acSaveYes
        End If
    Next

CloseAllOpenForms_End:
    On Error Resume Next
    Set frm = Nothing
    Err.Clear
    Exit Sub

CloseAllOpenForms_Err:
    'MsgBox "Error: " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
    "in Sub: CloseAllOpenForms", vbCritical, "Error in Application"
    Err.Clear
    Resume CloseAllOpenForms_End
End Sub

 

[Kayleigh]

Thanks but would like to keep some forms open. I am more concerned about open references to objects which are no longer being used.

 

[Minty]

The object as far as access is concerned will be closed if the module opening it is closed e.g. a form module.
If you open an object in another separate module then I'm unsure of the scope.
I suspect the object will be closed as far as access is concerned if it is declared in a particular sub or function, if it is declared outside of that it will remain open unless specifically closed.

That won't help if for instance you open an excel object and have a crash. The Access pointer will be destroyed when you reset the code, but the actual object (Excel app) will remain as an orphaned process.

 

[Kayleigh]

So only option is to specifically close it in the function it is opened in?

 

[The_Doc_Man]

That is generally preferred. The idea is derived from that old book "Everything I Needed to Know I Learned in Kindergarten." From that bit of literary wisdom comes these concepts:

If you take it out, put it back.
If you open it, close it.
If you want to use something popular, be willing to share.

When dealing with external objects (and you ARE doing that when talking about e-mails sent through Outlook or Thunderbird or some other utility), you have to remember that the external object has NO CLUE about the structures you opened in Access. External objects have their own separate existence and their own separate memory. When you commune with that object, you are opening a channel (somewhat akin to a network link, but internal to your 'puter). You are NOT sharing common memory with those objects. Which means the external object is trusting YOU to clean up after yourself.

Another factor is that if you leave something open that potentially is writing something to a disk, that Access uses buffering methods to hold whole chunks of a file until it has enough to make a write-back worth its while. So that still-open object MIGHT be partly on disk, partly in memory. While there are those who don't bother to close recordsets but rather just let the "EXIT SUB" code handle open items, the catch is that you would want to force any open items to be complete before actually using them. Otherwise, there is no telling what is still hanging around - and where.

Note that for recordsets, at least, they WILL clean themselves up on subroutine exit most of the time, though there is a known issue with the SMB v2 and v3 protocols regarding networking "reservations." Closing recordsets when you are finished with them is still probably a good idea.

 

[Pat Hartman]

I am more concerned about open references to objects which are no longer being used.

Access didn't read the Kindergarten book and it does not share well. I think if you explicitly close the object from Access, Access releases its lock but if you close the file from the app that was using it, Access keeps the lock until the db closes.

 

[The_Doc_Man]

I think if you explicitly close the object from Access, Access releases its lock but if you close the file from the app that was using it, Access keeps the lock until the db closes.

If you OPEN a file in Access and close it from Access, Access releases that lock. If you open a file using an external utility such as Outlook, then the main lock is owned by that utility. Access can have a passive "interest" lock since a file creator (in Office) by default takes out an exclusive-write lock. It's a matter of lock ownership. And, if the file is being created, normally that file cannot be modified by a second task. Yes, I know that VBA's OPEN verb includes the "Read" and "Write" accessibility options, but I believe that WRITE would be disallowed to a second task when the first task is opening that file.

Now if someone "diddles" with the locks using Win32 API calls or more modern equivalents, all bets are off. However, using normal OPEN and CLOSE verbs, no task can close a file that it didn't open unless it can force the owning task to do so. (Among other things, it doesn't have the original file handle.) And in this case, "force closure" means kill the owning task unless you are an admin AND can identify the handle on the file in question. There are CMD window utilities for that, but Access doesn't normally have a means to use them.

 

[gemma-the-husky]

it depends how and when your objects are closed.

if you have public objects such as recordset variables or objects, then you can list them all, and close them. You only have to add the code once. I imagine you could put them inside class modules and make it even slicker, but this would do me.

this is how I dispose of my persistent connection when I close a database.

if not recordset1 is nothing then
close recordset1
set recordset1 = nothing
end if

if not recordset2 is nothing then
close recordset2
set recordset2 = nothing
end if

if not accessobject is nothing then
set accessobject = nothing
end if

etc

 

[Pat Hartman]

Doc, I'm talking about opening a word doc or spreadsheet using automation. If you close the doc using word or excel, the Access lock remains but if you close the file from Access, it is released. Is that the way it works?

 

[Cronk]

I'm surprised that nobody has commented on the flaw in the code in #2. The loop should be

For iVal =  iValX - 1 to 0 step -1

not

For iVal = 0 To iValX - 1

For newer users, the issue can be demo'd by the example of 3 open forms, frmA, frmB and frmC opened in that order. Forms(0) will be frmA, forms(1) frmB and forms(2) , frmC.

If the loop is 0 to 2, on the first pass, frmA will be closed. FrmB will become forms(0) and frmC will become forms(1)

On the second loop pass, frmC will be closed.

On the third and final pass, there is only one form open, frm(B) which is forms(0). Trying to close forms(2) will generate a run time error.

Reversing the order of the loop will successive close frmC, frmB and finally frmA with each form maintaining its Form ranking until it is closed.

 

[The_Doc_Man]

Doc, I'm talking about opening a word doc or spreadsheet using automation. If you close the doc using word or excel, the Access lock remains but if you close the file from Access, it is released. Is that the way it works?

The lock is owned by and associated with the process that opened it. If you open an application object (Word or Excel are the two most common cases), you create a separate task that can remain in memory if Access doesn't force it to quit first. Outlook is another case in point. Haven't tried PowerPoint and really have no curiosity on that case.

I've done that trick of leaving behind an open app task more times than I care to remember before I finally learned the lesson. Forcing the Application.Quit closes all of the app task's files and releases all of its locks.

In any case, for this discussion the subject file is opened through VBA/automation of the app-related task and that means the file lock is associated with the automated task, not with Access. To do otherwise would violate the "task separation" rule that applies to all Windows tasks. Unless Access ALSO opens the same file as a separate action, there is no Access-related lock in this case.

To the best of my knowledge, when you create an app object that CAN have a life of its own, it is NOT a child of Access in the app task tree - it is a co-equal that can (does) persist after Access exits unless you force the app to quit. The bad news is, of course, that once Access exits, the app has no controlling devices at that point because it was opened using a network channel that goes through the LOOPBACK port (I think) and its controlling task died without saying goodbye. There IS a way to create a child process that would be self-cleaning on the death of a parent task, but it requires use of an API call.

The bottom line? If you allow the app object to persist past the lifetime of the Access session, the lock also persists.

 

[Pat Hartman]

So, it is the hidden instance of Excel that is holding the lock? When I use automation, I always open a new instance of Word or Excel so I can explicitly close it when I am done. It is really bad to close an instance that the user opened and you just borrowed

 

[GPGeorge]

Me. Me. Ask me why "It is really bad to close an instance that the user opened and you just borrowed". The scar is barely visible these days, though.

 

[Pat Hartman]

Someday we should compare scars over a nice pinot noir and count coup to see who has made the most egregious errors. I'll tell you the story about when the lead operator walked into the big room my team was working in and started throwing rings (from the 9-track tapes - no ringy no writey) at me from across the room. His aim was pretty good too

 

[Pat Hartman]

OK, who knows what counting coup is