Access 2003: run code on database close

Question

In Access 2003, there are ways of running code when a form or database is opened, but what about when the database is closed?

My motivation is the unavoidable use of a somewhat buggy third-party COM library. Releasing the COM reference (by setting the variable to Nothing) causes it (correctly) to disconnect from its server. The trouble is it can't then re-connect without exiting the process and starting a new one (which is a known bug). In this case, the process is the whole Access IDE :(

Ideally I'd like to store the COM reference somewhere that would be protected from the VBA "Reset" action which clears global variables (and is common during debugging, sometimes forced by a code edit). But then I would like to have the chance to clean up before the database is closed.

Answer 1

don't release the reference. Let Access do that when it closes.

Answer 2

You could run code at database close if you have a form which you set to automatically open at database startup ... and leave the form open. Then you can use the form's On Close event to run your cleanup code:

Private Sub Form_Close()
    'do your stuff here '
End Sub