After setting data in AppDomain within MS-Access VBA why does Access refuse to shutdown properly?

Question

Edited Note: Thanks to Mathieu Guindon for making me aware of the TempVars collection! Between that and local tables for arrays I will be able to skip the use of the appdomain.

I am using the attached code to keep global variables in MS-Access. However, after Access Closes it will not reopen properly because it never fully left the Task Manager.

Is there something I can do within VBA to ensure it shuts down properly? I am using MS Access 2010 as a front end to an SQL Server 2012 Backend.

I have concluded that this issue only occurs when I added the code to utilize the DefaultAppDomain to store the scripting dictionary, and hoping there is some cleanup code on the domain side I could execute to allow the App to close fully.

Private Function GetDomainDict() As Object

    Const vName = "dict-data"
    Static vDict As Object

    If vDict Is Nothing Then
      Dim vDomain As mscorlib.AppDomain
      Dim vHost As New mscoree.CorRuntimeHost
      'get the vDomain from .net
      vHost.Start
      vHost.GetDefaultDomain vDomain

      'Get the Disctionary for this app from .net or create it
      If IsObject(vDomain.GetData(vName)) Then
        Set vDict = vDomain.GetData(vName)
      Else
        Set vDict = CreateObject("Scripting.Dictionary")
        vDomain.SetData vName, vDict
      End If
    End If

    Set GetDomainDict = vDict
End Function

Public Sub StGV(vVarName As String, vVar As Variant)
    'Set Global Variable Dictionary to the dictionary saved in .net for this Application
    Set pGlobals = GetDomainDict()
    'Check if variable exists and set value either way
    If pGlobals.Exists(vVarName) Then
        pGlobals(vVarName) = vVar
    Else
        pGlobals.Add vVarName, vVar
    End If
End Sub

Public Function GtGv(vVarName As String)
    'Set Global Variable Dictionary to the dictionary saved in .net for this Application
    Set pGlobals = GetDomainDict()
    'Check if variable exists return its value or null if nonexistant
    If pGlobals.Exists(vVarName) Then
        GtGv = pGlobals(vVarName)
    Else
        GtGv = Null
    End If
End Function

Before comments

I designed a taskkill to ensure access shuts down. That was what was closing the app. I would rather not rely on that.

After Comments...

If I do not use "as new" the variables do not seem to load and I get object with or variable not set error, but I did add the set = nothing. I implemented the .stop and I tried doing an unload on exiting the frontend, but I get an automation error -2146234347. I forced a close afterwards by clicking the database window close button, but access still does not shut down. New Code

Dim vDomain As mscorlib.AppDomain
Dim vHost As New mscoree.CorRuntimeHost

'get the vDomain from .net
vHost.Start
vHost.GetDefaultDomain vDomain
'folowing line errors out automation error
vHost.UnloadDomain vDomain
vHost.Stop
Set vDomain = Nothing
Set vHost = Nothing

DoCmd.Quit


End Sub
Private Function GetDomainDict() As Object

    Const vName = "dict-data"
    Static vDict As Object

    If vDict Is Nothing Then
      Dim vDomain As mscorlib.AppDomain
      Dim vHost As New mscoree.CorRuntimeHost
      'get the vDomain from .net
      vHost.Start
      vHost.GetDefaultDomain vDomain
      'Get the Disctionary for this app from .net or create it
      If IsObject(vDomain.GetData(vName)) Then
        Set vDict = vDomain.GetData(vName)
      Else
        Set vDict = CreateObject("Scripting.Dictionary")
        vDomain.SetData vName, vDict
      End If
        vHost.Stop
        Set vDomain = Nothing
        Set vHost = Nothing
   End If

    Set GetDomainDict = vDict
End Function

Public Sub StGV(vVarName As String, vVar As Variant)
    'Set Global Variable Dictionary to the dictionary saved in .net for this Application
    Set pGlobals = GetDomainDict()
    'Check if variable exists and set value either way
    If pGlobals.Exists(vVarName) Then
        pGlobals(vVarName) = vVar
    Else
        pGlobals.Add vVarName, vVar
    End If
End Sub

Public Function GtGv(vVarName As String)
    'Set Global Variable Dictionary to the dictionary saved in .net for this Application
    Set pGlobals = GetDomainDict()
    'Check if variable exists return its value or null if nonexistant
    If pGlobals.Exists(vVarName) Then
        GtGv = pGlobals(vVarName)
    Else
        GtGv = Null
    End If
End Function `

Answer 1

Just to offer an alternative to keep it purely VBA and avoiding a new dependency on .NET, you could consider using TempVars collection which was introduced in Access 2007. This will survive a VBIDE reset. Note that it can only store primitive data types (e.g. strings, numbers, dates but not objects) and it's globally available.

You also can consider using a local Access table as a data store and read/write to it instead, which also would survive the reset and also would survive the restart which may be desirable or not, depending on what you need the variables for. In the case where you'd rather that it didn't survive the restarts, it's easy enough to just clear the table.

I like the idea of using AppDomain to provide an in-memory data store which won't survive the restart but would the reset but it does feel heavy-handed, particularly if that's the only use of .NET CLR you have. I'm also a bit concerned about the comment:

The reason I use this is that any untrapped error in this causes all global variables to clear in access vba.

That is not the normal state of affair. Normally an untrapped error will bring up a VBIDE error message where you can debug or end. It should be noted that clicking End button is resetting the state, just as is clicking the Stop button or executing the Stop statement. For development, using Debug button and exiting the procedure normally (e.g. by moving the yellow arrow to the exit) will avoid the reset of the state. For users' experience, they will never see such "reset" except in the case of using an ACCDE or running the Access in a runtime mode which means there's no VBIDE available to debug so the only meaningful thing to do is to well, end things and exit. In that case, that means the error should have been trapped in the first place.

If error handling is a large enough issue, you might want to consider using a commerical third-party add-in such as vbWatchDog which helps enormously with improving the error UX, especially for an ACCDE or runtime environment.

IOW, I'm a bit concerned that the AppDomain idea is solving a developer-only problem.