VBA Code to Close an Access Database Without Leaving a Shell of the application open

Question

I'm currently using Application.Quit which leaves a shell of the MS Access Application open.

After processing the calling function against the target application, I am left with every instance of the application remaining open still; even though the specific database instance is closed.

How do I make the database 'shell' application window close programatically using VBA?

Here is an example of how the variable is created and how I am closing it:

Dim appAccess As New Access.Application

' Do stuff here...

appAccess.CloseCurrentDatabase

Answer 1

According to the documentation: Application.Quit does the same thing as DoCmd.Quit. Namely

The Quit method quits Microsoft Access. You can select one of several options for saving a database object before quitting.

You can try calling either of them with the parameter acQuitSaveNone or 2 which "Quits Microsoft Access without saving any objects". Upon further review, use Application.Quit as DoCmd.Quit was added for backward compatibility for Access 95 (See remarks for Quit Method as it applies to the DoCmd object.) Doing either of these should still do an automatic compact on close if you have the permissions, which may be the cause of you shells.

If that doesn't work for you, here is a somewhat extreme suggestion. Save this as a vbscript file and call it once you're truly done with Access. This will terminate all MSAccess processes on your windows pc, without compacting and repairing.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Process WHERE Name = 'msaccess.exe'")
For Each objProcess in colProcessList
    objProcess.Terminate()
Next

To call the script replacing [vbspath] with the actual path. If the path has spaces make sure to use double quotes and put it in quotes:

shell "cscript [vbspath]"