Access instance remains in memory once closed

[CJ_London]

I have an app which creates an Access instance and populates it with another db that the user selects for analysis. Even if I do nothing and then close the instance it remains in memory. It was reported as a bug about 3 years ago (although that seems to be more about closing access in general, not a separate instance). And was fixed back then.

I'm using 365 64bit Office and the app itself appears to be closed and the lockfile gone, it's just the instance remains in memory. This is the code to open/close the instance and assign a db. The only modification is to disable the accApp minimize and close buttons if the user were to open it to view.

I've had lengthy 'conversations' with AI but they are unable to provide a valid solution. Much talk of leaving form/report references open,

This is the code to open/close accApp - copy and paste it to a db of your choice and supply a dbName (to include path). Have task manager open to see what happens on creation and destruction. When choosing a db, ensure you do not have a startform specified and autoexec macro is disabled. to ensure no code runs

Public accApp As Access.Application

Sub CloseOpenDB(dbName As String)
Static oldName As String
Dim lStyle As LongPtr
Dim frm As Object

    If dbName = "" Then 'close accApp
        If Not accApp Is Nothing Then
            If Not accApp.CurrentDb Is Nothing Then 'close db
             'If Not accApp.CurrentProject Is Nothing Then 'close db
                For Each frm In accApp.CurrentProject.AllForms
                    If accApp.SysCmd(acSysCmdGetObjectState, acForm, frm.Name) = acObjStateOpen Then
                        accApp.DoCmd.Close acForm, frm.Name
                    End If 'accApp.SysCmd....
               
                Next frm
                Set frm = Nothing
                accApp.CloseCurrentDatabase

            End If 'Not accApp.CurrentDb Is Nothing

            accApp.Quit acQuitSaveNone
            'accApp.DoCmd.Quit acQuitSaveNone (no difference with either option)
            DoEvents
            Set accApp = Nothing
            DoEvents
            Debug.Print accApp Is Nothing
           
        End If 'Not accApp Is Nothing

        oldName = ""
        Exit Sub
   
    End If 'dbName = "" (close instance)
   

    '****** Open a new db ******
    If dbName <> oldName Then
        If accApp Is Nothing Then 'open accApp
            Set accApp = New Access.Application '***************** at this point the app appears in background processes in task manager
            accApp.AutomationSecurity = 3 'msoAutomationSecurityForceDisable
             'accApp.Visible = False 'default, change to true if you wish - in which case it moves to the Apps section in task manager

 '********************** the following code can be commendeted out for testing - otherwise add in the relevant API's and constants
'            'disable close button
            RemoveMenu GetSystemMenu(accApp.hWndAccessApp, False), SC_CLOSE, MF_BYCOMMAND
            DrawMenuBar accApp.hWndAccessApp
         
            ' Remove Minimize button
            lStyle = GetWindowLongPtr(accApp.hWndAccessApp, GWL_STYLE)
            lStyle = lStyle And Not WS_MINIMIZEBOX
            SetWindowLongPtr accApp.hWndAccessApp, GWL_STYLE, lStyle
            SetWindowPos accApp.hWndAccessApp, 0, 0, 0, 0, 0, _
                    SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOZORDER Or SWP_FRAMECHANGED
'****************************** comment out to here

        ElseIf dbName <> accApp.CurrentDb.Name Then
            accApp.CloseCurrentDatabase
           
        End If 'accApp Is Nothing
       
        DoEvents
       
    End If 'dbName <> oldName
   

    'assign new db
    accApp.OpenCurrentDatabase dbName, False
    accApp.UserControl = False
    oldName = dbName
   
End Sub

code to use (even from the immediate window)
CloseOpenDB "C:\path\dbname.accdb"
CloseOpenDB ""

So is anybody doing something like this - from a 'master' Access app, opening a db in another access instance created in the master app? If so, do you experience the same issue? If not, would you mind sharing your solution for opening/closing the app?

And if someone would like to try the above code - do you experience the same issue?

Edit - connecting via DAO does not cause an issue, but this app is looking at the objects rather than the data and the objects cannot be analysed from DAO

 

[The_Doc_Man]

Objects will remain in memory if they have a file still open. Remember that individual recordsets are treated as individual file channels. There is also the question of whether the database you opened itself opens an application object that it doesn't close. The app.Quit SHOULD do the right thing but there is the question of whether it is stuck in an I/O rundown with an open channel.

 

[bbfromgb]

MS Help for Access states:
If you use the CloseCurrentDatabase method to close the database that is open in the current instance of Microsoft Access, you can then open a different database without having to create another instance of Microsoft Access.

MS Help states for creating the new instance of Microsoft Access from another application through Automation:

' Create new instance of Microsoft Access.
Set accApp = CreateObject("Access.Application")

You are using:

Set accApp = New Access.Application '***************** at this point the app appears in background processes in task manager

Im not sure, but that might make the difference

 

[CJ_London]

Thanks for the responses

My understanding is the difference between ..AS New and createObject is AS is early binding and creatobject is late binding. However I have tried both ways with the same result, regardless of how accApp is declared.

To go back to the simplest test try this code change the options by commenting/uncommenting:

Sub testAccessInstance()
Dim acc As Access.Application
'Dim acc As object

Set acc = New Access.Application
'Set acc = CreateObject("Access.Application")

acc.AutomationSecurity = 3
'Stop

'acc.Quit acQuitSaveNone
acc.DoCmd.Quit acQuitSaveNone
Set acc = Nothing

End Sub

In all cases, the instance remains in memory. One interesting glitch is if you use acc.Quit acQuitSaveNone then the system hangs until you delete the instance in task manager - although that doesn't happen in the initial code I provided - the main difference being I assign a db to the instance. Docmd.quit is provided for backward compatiblity.

So no objects open, no recordsets - just an object set then closed. I'm beginning to wonder if this is perhaps a bug?

 

[isladogs]

I can't see the issue you describe using your code (with the specified section commented out)
No additional instances remain in Task Manager and Access doesn't hang for me.

EDIT I tested in 365 version 2509 build 19220.20000 Beta 64-bit. It may be relevant as to which version build / bitness / channel you are using

If you are on 365 version 2508 or later there may be additional WebView instances in task manager which are needed for Monaco speedy loading

How Speedy Loading Works in the Monaco SQL Editor

This article explains how the new speedy loading feature works by preloading the Monaco editor in the background during idle time. The result is that Monaco SQL editor windows now load almost instantaneously.

isladogs.co.uk

In case it helps, here is my generic code for opening/closing external databases with multiple options for different scenarios.

Public Function RunExternalDatabase() As Boolean
    
    Dim app As Access.Application, strPath As String
    'Start a new MSAccess application
    Set app = New Access.Application
    
    'Open the remote database, then close the current (or remote) database
    With app
        'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
        
       '  strPath = "C:\Programs\MendipDataSystems\JATFA\JATFA.accdb" 'replace with your file path
        ' strPath = "C:\Programs\MendipDataSystems\SDALink\SDALink.accdb" '/cmd AttMarks"
         strPath = CurrentProject.Path & "\TEST.accdb" 'full file path to your database
         .OpenCurrentDatabase strPath, False 'no db password
        
     '   .OpenCurrentDatabase strPath, True, "password" 'for use if password exists
         .Visible = True
         .UserControl = True
        
    '    .DoCmd.RunMacro "mcrRefreshPersTable" 'run your macro
        .CloseCurrentDatabase 'closes external database as that is current
    End With
    
    'Quit the spawned app - DISABLED as not wanted here
    'app.Quit acQuitSaveNone
    'Set app = Nothing
    
    'Quit the current app
   Application.Quit acQuitSaveNone
    
End Function

 

[RonPaii]

I have an app which creates an Access instance and populates it with another db that the user selects for analysis. Even if I do nothing and then close the instance it remains in memory. It was reported as a bug about 3 years ago (although that seems to be more about closing access in general, not a separate instance). And was fixed back then.

I'm using 365 64bit Office and the app itself appears to be closed and the lockfile gone, it's just the instance remains in memory. This is the code to open/close the instance and assign a db. The only modification is to disable the accApp minimize and close buttons if the user were to open it to view.

I've had lengthy 'conversations' with AI but they are unable to provide a valid solution. Much talk of leaving form/report references open,

This is the code to open/close accApp - copy and paste it to a db of your choice and supply a dbName (to include path). Have task manager open to see what happens on creation and destruction. When choosing a db, ensure you do not have a startform specified and autoexec macro is disabled. to ensure no code runs

Public accApp As Access.Application

Sub CloseOpenDB(dbName As String)
Static oldName As String
Dim lStyle As LongPtr
Dim frm As Object

    If dbName = "" Then 'close accApp
        If Not accApp Is Nothing Then
            If Not accApp.CurrentDb Is Nothing Then 'close db
             'If Not accApp.CurrentProject Is Nothing Then 'close db
                For Each frm In accApp.CurrentProject.AllForms
                    If accApp.SysCmd(acSysCmdGetObjectState, acForm, frm.Name) = acObjStateOpen Then
                        accApp.DoCmd.Close acForm, frm.Name
                    End If 'accApp.SysCmd....
              
                Next frm
                Set frm = Nothing
                accApp.CloseCurrentDatabase

            End If 'Not accApp.CurrentDb Is Nothing

            accApp.Quit acQuitSaveNone
            'accApp.DoCmd.Quit acQuitSaveNone (no difference with either option)
            DoEvents
            Set accApp = Nothing
            DoEvents
            Debug.Print accApp Is Nothing
          
        End If 'Not accApp Is Nothing

        oldName = ""
        Exit Sub
  
    End If 'dbName = "" (close instance)
  

    '****** Open a new db ******
    If dbName <> oldName Then
        If accApp Is Nothing Then 'open accApp
            Set accApp = New Access.Application '***************** at this point the app appears in background processes in task manager
            accApp.AutomationSecurity = 3 'msoAutomationSecurityForceDisable
             'accApp.Visible = False 'default, change to true if you wish - in which case it moves to the Apps section in task manager

 '********************** the following code can be commendeted out for testing - otherwise add in the relevant API's and constants
'            'disable close button
            RemoveMenu GetSystemMenu(accApp.hWndAccessApp, False), SC_CLOSE, MF_BYCOMMAND
            DrawMenuBar accApp.hWndAccessApp
        
            ' Remove Minimize button
            lStyle = GetWindowLongPtr(accApp.hWndAccessApp, GWL_STYLE)
            lStyle = lStyle And Not WS_MINIMIZEBOX
            SetWindowLongPtr accApp.hWndAccessApp, GWL_STYLE, lStyle
            SetWindowPos accApp.hWndAccessApp, 0, 0, 0, 0, 0, _
                    SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOZORDER Or SWP_FRAMECHANGED
'****************************** comment out to here

        ElseIf dbName <> accApp.CurrentDb.Name Then
            accApp.CloseCurrentDatabase
          
        End If 'accApp Is Nothing
      
        DoEvents
      
    End If 'dbName <> oldName
  

    'assign new db
    accApp.OpenCurrentDatabase dbName, False
    accApp.UserControl = False
    oldName = dbName
  
End Sub

code to use (even from the immediate window)
CloseOpenDB "C:\path\dbname.accdb"
CloseOpenDB ""

So is anybody doing something like this - from a 'master' Access app, opening a db in another access instance created in the master app? If so, do you experience the same issue? If not, would you mind sharing your solution for opening/closing the app?

And if someone would like to try the above code - do you experience the same issue?

Edit - connecting via DAO does not cause an issue, but this app is looking at the objects rather than the data and the objects cannot be analysed from DAO

It may not help, but try add a set frm = Nothing inside the loop

            If Not accApp.CurrentDb Is Nothing Then 'close db
             'If Not accApp.CurrentProject Is Nothing Then 'close db
                For Each frm In accApp.CurrentProject.AllForms
                    If accApp.SysCmd(acSysCmdGetObjectState, acForm, frm.Name) = acObjStateOpen Then
                        accApp.DoCmd.Close acForm, frm.Name
                    End If 'accApp.SysCmd....
                    Set frm = Nothing
                Next frm
                Set frm = Nothing
                accApp.CloseCurrentDatabase

            End If 'Not accApp.CurrentDb Is Nothing

 

[CJ_London]

Thanks for the replies - I’m out and about until late, so will reply tomorrow after more testing

 

[CJ_London]

OK - I've done some more testing and tried Colin's code - but still hit the same issue - basically app.quit doesn't appear to do anything and it locks up on Set app=nothing until I manually end task in task manager. Summary of events when stepping through the code:

Set app = New Access.Application - Microsoft Access appears in background processes
.OpenCurrentDatabase strPath, False - Microsoft Access remains in background processes
.Visible = True - Microsoft Access move3s from background processes to Apps
.UserControl = True - in the Apps section Microsoft Access opens a sub level to display the db path and name
.CloseCurrentDatabase - in the Apps section, the sublevel remains open, but the path/name disappear (noting ht the background processes
app.Quit acQuitSaveNone - Microsft Access moves from the Apps Section reappears in the background process section, then disappears
Set app = Nothing - nothing happens

But when running without stops - Microsoft Office does not disappear from the Background processes - see the attached zip file which contains a mp4 file.

my version is 2507:

View attachment 121276

but I note that is earlier than Colin's version so I ran updates (which is supposed to be automatic. So now I am on

Still not as current as Colin's - and still has the same problem

I'm just going to reboot - see if that resolves the issue, so posting now in case I lose the above

Nope - rebooted and same problem (and appear to have lost the screenshot to previous version above). Do I need to join the insider program to get v2509? or is there another way?

 

[The_Doc_Man]

According to Google's Gemini, you are doing this to yourself.

When Access is launched manually, the help page on .UserControl says that property is read-only in all views. HOWEVER, when Access is launched via VBA, the user CAN set it TRUE or FALSE as needed. So... I asked Google Gemini this question:

Is there any effect from setting application.usercontrol to TRUE?

Google Gemini says:

The main effect of setting Application.UserControl to True is to prevent the application from closing automatically when the script that opened it finishes. This provides a way to make the application visible and keep it open for the user.

I note that in your code, you DO set .UserControl to TRUE. Try setting it to FALSE before you are about to close that app object. See if that makes it go away.

 

[isladogs]

OK I've done some more testing with both the original code from post #1 and a modified version of my generic code in post #5

I still cannot replicate the additional Access instance remaining in Task Manager after running your code straight through or stepping through it.
I re-tested altering .AutomationSecurity = 3 to both 1 and 2 and then removing it.
In all cases, any startup form & autexec macro still runs which I think confirms my original thought that the line serves no benefit in your code
In all cases, the code then completed & the external app closed (including in task manager)

If you want to prevent any code running in the external app, open it using the shift bypass.

Using my generic code, it needs to be adapted for your purposes:

Public Function RunExternalDatabase() As Boolean
    
    Dim app As Access.Application, strPath As String
    'Start a new MSAccess application
    Set app = New Access.Application
    
    'Open the remote database, then close the current (or remote) database
    With app
        'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
        
         strPath = "C:\FullDatabasePath.accdb" 'full file path to your database
         .OpenCurrentDatabase strPath, False 'no db password
     '   .OpenCurrentDatabase strPath, True, "password" 'for use if password exists
        
       '  .Visible = True 'optional
       '  .UserControl = True 'optional
        
        .CloseCurrentDatabase 'closes external database as that is current
    End With
    
    'Quit the spawned app
    app.Quit acQuitSaveNone
    Set app = Nothing
    
    'Quit the current app - NOT NEEDED HERE
    'Application.Quit acQuitSaveNone
    
End Function

You can choose whether or not to makes the external app visible or allow user interaction with it if it stays open.
None of those variations should cause a leftover instance in Task Manager after closing the external app.

Version 2509 is Beta Channel so you would need to join the Insider program to obtain this.
I very much doubt that your issue is related to your CC version however.

 

[arnelgp]

maybe you can "force" kill the Access app, from CoPilot:

Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr

Private Declare PtrSafe Function TerminateProcess Lib "kernel32" _
    (ByVal hProcess As LongPtr, ByVal uExitCode As Long) As Long

Private Declare PtrSafe Function CloseHandle Lib "kernel32" _
    (ByVal hObject As LongPtr) As Long

Private Const PROCESS_TERMINATE As Long = &H1


Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hWnd As LongPtr, lpdwProcessId As Long) As Long


Public Function KillProcessByPID(pid As Long) As Boolean
    Dim hProcess As LongPtr
    Dim result As Long

    hProcess = OpenProcess(PROCESS_TERMINATE, False, pid)
    If hProcess <> 0 Then
        result = TerminateProcess(hProcess, 0)
        Call CloseHandle(hProcess)
        KillProcessByPID = (result <> 0)
    Else
        KillProcessByPID = False
    End If
End Function


Public Function GetPIDFromHwnd(hWnd As LongPtr) As Long
    Dim pid As Long
    Call GetWindowThreadProcessId(hWnd, pid)
    GetPIDFromHwnd = pid
End Function

' this is it
Public Sub KillProcessByWindowHandle(hWnd As LongPtr)
    Dim pid As Long
    pid = GetPIDFromHwnd(hWnd)
    If pid <> 0 Then
        If KillProcessByPID(pid) Then
            'MsgBox "Process terminated successfully."
        Else
            'MsgBox "Failed to terminate process."
        End If
    Else
        'MsgBox "Could not retrieve PID from hWnd."
    End If
End Sub

on your code that closes the, save the Window Handler:

Sub CloseOpenDB(dbName As String)
Static oldName As String
Dim lStyle As LongPtr
Dim frm As Object

Dim hWnd As LongPtr

    If dbName = "" Then 'close accApp
        If Not accApp Is Nothing Then
       
            hWnd = accApp.Application.hWndAccessApp

            If Not accApp.CurrentDb Is Nothing Then 'close db
...
...
            End If 'Not accApp.CurrentDb Is Nothing

            accApp.Quit acQuitSaveNone
            'accApp.DoCmd.Quit acQuitSaveNone (no difference with either option)
            DoEvents
            Set accApp = Nothing
            DoEvents
            Debug.Print accApp Is Nothing
          
            'Terminate it
            KillProcessByWindowHandle hWnd           '<==
           
        End If 'Not accApp Is Nothing

        oldName = ""
        Exit Sub
...
...

 

[isladogs]

I have my own article about 4 different ways of forcibly closing Access where necessary:

• taskkill
• Powershell Stop-Process
• EndTask API
• GetProcessID API

Forcibly Shutdown Access

This article discusses several different ways of forcibly shutting down Access if necessary when problems occur. Warnings are given about the risks of doing so.

www.isladogs.co.uk

Whilst all of these methods work, they should only be used where absolutely necessary

However, as already stated, nothing in either @CJ_London's code nor my own should result in a hanging Access instance in task manager

 

[arnelgp]

However, as already stated, nothing in either @CJ_London's code nor my own should result in a hanging Access instance in task manager

then what is the purpose of creating this thread?

So is anybody doing something like this - from a 'master' Access app, opening a db in another access instance created in the master app? If so, do you experience the same issue? If not, would you mind sharing your solution for opening/closing the app?

And if someone would like to try the above code - do you experience the same issue?

 

[CJ_London]

Using Colin's code (with the exception of the target file which does not have autoexec or a specified startform) I still get the problem - see the attached video which shows step by step by step what happens in task manager on execution of each line. Note the comment on set app=nothing needs updating - it seems to only freeze when stepping through the code, but I still need to manually end task for the instance

I've also tried to code Arnel provided and that does work (KillPorcess replaces docmd.quit) although copilot does say it is a method of last resort (per Colin's comment). Many thanks Arnel

you are doing this to yourself.

I've tried commenting out both visible and usercontrol - no change, the instance remains open.

I have no idea why Colin's code works for him and not for me - the only thing that I can see that might be different is the version. When I get updated to 2509 I will revisit.

Many thanks for all responders - I'll leave the thread open for now, see if anyone else experiences the same issue

 

[arnelgp]

there is no harm forcing to kill Access app.
your code already Closed the db properly.
what is left (stubborn) is the Access app.
you don't corrupt an EXE by killing it's process
(same when you use Task Manager to force-kill it).

 

[isladogs]

Don’t forget your own code also works for me as long as I follow it with CloseOpenDB “”.

As I haven’t heard other reports of leftover instances in CC 2507 / 2508, at the moment I suspect it’s an artefact on your machine.

Do you still have 2010 installed as well and if so, which is the default version?

 

[arnelgp]

just tested your code, and i get the msg "Process terminated successfully" (after uncommenting all msgbox).
it means, that still the other instance of Access is still alive when i issue the command:

accApp.Quit

 

[CJ_London]

Do you still have 2010 installed as well and if so, which is the default version?

No - I have a new machine on win 11 with 365. Still have it in a virtual machine I ported over from my old machine - I can take a look later

 

[isladogs]

Just in case its relevant, I'm still on Win 10

 

[AHeyne]

@CJ_London :
- Maybe you have any Microsoft Access Add-In installed which causes this behaviour?
- Or can you disable any anti-virus software and test again?