Access instance remains in memory once closed

C

CJ_London

Super Moderator
Staff member
Local time
Today, 21:52
Joined
Feb 19, 2013
Messages
17,669
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

Code: 
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
 
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.
 
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:
Code: 
' Create new instance of Microsoft Access.
Set accApp = CreateObject("Access.Application")
You are using:
Code: 
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
 
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:
Code: 
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?
 
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
isladogs.co.uk

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 isladogs.co.uk

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

Code: 
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
 
Last edited:
CJ_London said:
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

Code: 
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
Click to expand...
It may not help, but try add a set frm = Nothing inside the loop

Code: 
            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
 
Thanks for the replies - I’m out and about until late, so will reply tomorrow after more testing
 
You must log in or register to reply here.

Similar threads

A
Solved Connection String mysterious Trim
Replies
3
Views
212
cheekybuddha
cheekybuddha
riktek
Closing Form Instances
Replies
8
Views
854
Edgar_
E
A
Solved External Database Update Connection String
Replies
9
Views
586
Pat Hartman
P
L
REFERENCING Form's instances controls
2
Replies
39
Views
1,247
Mark_
M
K
Self-Healing Ribbon Pointer
Replies
0
Views
156
KevinBaker
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom