[2007] Execute code on Form Close when App Closes

[Solipcyst]

Hello,

I've created a dummy form that remains hidden while my front end app is open. I am attempting to execute a function during the Close event of this form which, it is my understanding, should take place when a user closes the app. However, it doesn't seem to work that way in all cases.

To be specific, I call a function that will remove a specific Access Reference (Excel). As you may have guessed, this is my attempt at version-proofing against Excel by calling a function from the AutoExec macro to add the Excel reference during start up which requires that no newer or missing/broken Excel reference already exist. Everyone using the app will have Excel, but not necessarily as new as 2007, which is what the development environment has.

Anyway, I can get this to work, but only if I close the database "properly" via command button or closing a Form that has "CloseCurrentDatabase" in its Close event. The next time I open the app, if I hold Shift to bypass AutoExec, I can check references and see that Excel is not marked. However, if I close the app via the red "X" for the Access windows itself, the removal of the excel reference doesn't seem to take.

I have confirmed that the function is successfully called and successfully executes by adding a simple MsgBox notification after the removal, but again, the removal doesn't "stick". Any idea what I might be missing here? Or is this just another undocumented "feature"?

Here's the relevant code:

Private Sub Form_Close()
    
    If fRemoveExcel = True Then
        MsgBox "Excel Removed."
        CloseCurrentDatabase
    Else: End If
    
End Sub

No matter if I close the app by closing the form or by clicking the "X" for Access, I get the MsgBox that tells me that the reference was successfully removed. Furthermore, I've debugged it with a breakpoint and followed the code step by step and confirmed that it executes properly as well.

Public Function fRemoveExcel() As Boolean

    Dim ref As Access.Reference
    Dim iCount As Integer
    Dim i As Integer
    
    '# Find Excel reference and remove it
    iCount = Application.References.Count
    For i = 1 To iCount
        Set ref = Access.References(i)
        If ref.Name = "Excel" Then
            Access.References.Remove ref
            fRemoveExcel = True
        Else
            fRemoveExcel = False
        End If
    Next
    Set ref = Nothing
    
End Function

 

[boblarson]

Why go through all of this hassle when you can version-proof your stuff simply by moving to LATE BINDING. Don't use Early Binding which requires the setting of the reference. Use Late Binding which doesn't.

I use early binding for development purposes but convert it all over to late binding for production use and therefore don't suffer from any version issues.

 

[Solipcyst]

Thank you for the reply. I was expecting this suggestion, but I forgot to mention that I'm taking over development of this app from someone else who did not use late binding and I really don't have the time to go through all of this poorly documented code to correct it.