Run Code on Database Closing

[gschimek]

Where would I access the event properties for when the entire database closes? Something like the On_Close event for a form, but for the whole database.

I want to have Access prompt to back up the database upon exit, so when someone clicks the close button, a dialog box opens and asks "Do you want to back up?" with an Yes or No option. Yes would obviously run the backup code, and No would simply exit.

 

[Moniker]

Look in Access help for the Quit Method.

Private Sub AppExit_Click()
    If MsgBox ("Do you want to backup?",vbYesNo+vbQuestion,"Backup DB?") = vbYes Then
        ---PERFORM BACKUP CODE HERE---
        Application.Quit 
    Else
        Application.Quit acQuitSaveNone
    End If

End Sub

 

[gschimek]

That looks good. But that appears to be code that I can attach to a command button. What if I want the code to run no matter how the program is closed, whether it be a command button, or clicking the X, or doing File/Exit? I would like all those options to run the Quit code. How can I do that?

 

[RuralGuy]

Open a hidden form first when you start up and then put your code in the exit event of the hidden form. Access will close this hidden form last and let you do what you want.

 

[gschimek]

That worked just fine. But now I'm having trouble with the backup script. I thought I'd just have the code call a batch file that copies the backend to a new location. The problem is that when I call the batch file, it opens and runs from c:\my documents\%username%\, so the relative paths in my batch file do not work.

Is there a way to have access tell run the batch file from command prompt in it's actual location? Or maybe it would be better just to copy the back end from within the VBA code. But I don't know how to do that, either. Maybe there's a way to use the Backup Database function under Tools/Database Utilities? With Sendkeys maybe? (but I don't know the code to do it).

 

[RuralGuy]

I would start a new thread with your last question.

 

[gschimek]

Good point. Thanks.