none
Office Application (e.g. MS-Access) cannot be closed after utilizing class in DLL??? A new insight? RRS feed

  • Question

  • This is an old problem posted in various forums that has never been solved to my knowledge.  There are work-arounds, but I have never seen an absolute answer or explanation regarding an Office application not being able to be closed (exited) after using certain methods or classes in a referenced DLL.  I believe I have stumbled onto one possible cause -- the Singleton class.

    My question is, is there any way to prevent the problem I am about to describe?  If an active DLL is the problem, is there any way for VBA to cause the Office application to explicitly "disconnect" from that DLL (so that the DLL process can terminate/die and garbage collect all its resources)?

    My hypothesis is that: If a singleton class is instantiated by code within a DLL (in my case, .NET and C#), then the mere existence of that object (and inability to proactively dispose of it) is what prevents the Office Application (say, MS-Access) from being able to exit.  I am having this problem and I do not encounter it so long as the Singleton in question is never instantiated (by the DLL, on behalf of my VBA code that utilizes classes within the DLL).

    Other posters have indicated that a singleton object is not truly garbage collected until the owning process itself has fully shut down.  Makes sense: the recommended Singleton pattern uses a read-only "instance" variable to reference itself.  Even if it were not read-only, I suspect that the object remains until the owning process truly dies.  For reference, here's the standard recommended singleton pattern:

    public sealed class MySingleton
    { 
        /*************************************/
        /* * *  D E C L A R A T I O N S  * * */
        /*************************************/
        private static readonly MySingleton instance = new MySingleton ();
    
        /*************************************/
        /* * *  C O N S T R U C T O R S  * * */
        /*************************************/
        private MySingleton () { }
    
        ~MySingleton
        {
            //instance = null  // <- this cannot be done due to being "read-only"
        }
    
        /*************************************/
        /* * * *  P R O P E R T I E S  * * * */
        /*************************************/
    
        public static MySingleton Instance
        { 
            get { return instance; }
        }
    
    } // end of class

    So, the question is:  Is it possible that the creation of a Singleton within DLL code (i.e., by any of its other classes, even just once) is the issue that prevents an Office application (containing VBA code that uses that DLL) from being able to be closed?  Is the application holding a "live" connection to the DLL that prevents the application from shutting down (in the case of MS-Access, it will not exit even after the database has been closed)?

    Thanks for any thoughts!

    P.S. I have not yet tried to re-design the Singleton to use a writable self-reference variable that can be set to null in the Singleton's deconstructor.  And as said, not sure that would make a difference.

    Saturday, February 18, 2017 3:56 PM

All replies

  • Okay, I was totally off base.  The Singleton has NOTHING to do with this issue. 

    It is much simpler. The problem seems to be created by merely passing a reference to the "Database" object in MS-Access as a method argument (from MS-Access VBA to a class method in the DLL).  The DLL seems to be hanging onto the Access application after that.  (Nulling all references to the class object is apparently not causing the needed garbage collection.)  MS-Access will not Exit after this; it keeps the main Access window open and refuses to close.  The Access process must be killed via Task Manager.

    To test, I created a new class library (COM-enabled) containing a single class having a single method.  Witness:

    public class ClassInMyDLL
    {
        public ClassInMyDLL () {}
    
        ~ClassInMyDLL () {}
    
        public int DoNothing ( Microsoft.Office.Interop.Access.Dao.Database db )
        {
            return 0;
        }
    }

    My test uses the Form_Load event handler of a simple Form containing a single label.  No matter, the problem is triggered from VBA code executing in any database Module.  I load and close this Form just once, to execute this code just once:

    Private Sub Form_Load()
        Dim i As Long, cls As ClassInMyDLL
    
        Set cls = New ClassInMyDLL
        i = cls.DoNothing(CurrentDb)
        Set cls = Nothing
    End Sub

    After that, the database can be closed, but MS-Access cannot be exited (the main window will not close). 

    So it seems that the DLL is somehow holding on to the CurrentDB reference, though it is never used, and this is preventing MS-Access from being able to shut down. 

    My purpose in all this is obviously to give a DLL some information to allow it to connect to and manipulate the database that Access has open.  This can be accomplished by passing other argument types to the DLL methods (like a string containing the path to the database file) and the problem is NOT encountered.  It seems to only be related to passing a reference to the CurrentDB object in a method argument. 

    Sure looks like a bug in the COM Interop layers and/or the Microsoft OLEDB driver.  BTW, this application is using Microsoft.ACE.OLEDB.12.0 and Access 2016, but I have encountered this problem with VBA code in Access 2010 and 2013 as well.

    So again, any thoughts on whether I am doing something wrong?


    P.S. This issue was unanswered in 2008. See: https://social.msdn.microsoft.com/Forums/en-US/a976337a-df2e-433b-ac0b-03901198116d/ms-access-wont-close-after-calling-a-function-in-dot-net-dll?forum=vssmartdevicesnative

    • Edited by Studio Tech Saturday, February 18, 2017 6:09 PM
    Saturday, February 18, 2017 5:48 PM
  • Hi Studio,

    I made a test with your description, and I could reproduce your issue.

    I agree with you that this issue is related with passing a reference to the CurrentDB.

    In my option, this is caused by that there is a reference to CurrentDB held by ClassInMyDLL. I would suggest you release the db in ClassInMyDLL.

    Here is a simple code:

            public int DoNothing(Microsoft.Office.Interop.Access.Dao.Database db)
            {
                Marshal.FinalReleaseComObject(db);
                return 2;
            }

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 20, 2017 7:03 AM