'System Resource Exceeded' Workaround Automation

[TesseractE]

(Access 2010)

So I think I may have found a way to purge the active cache that builds up in the Virtual Memory and causes the 'System Resource Exceeded' issue on 32-bit Office, but I'd love to be able to audtomate it in the background for my users.

I got the error today, then went to 'File' > 'Options' > 'Current Database'. I checked the box in 'Use the cache format that is compatible with Microsoft Access 2010 and later' and clicked OK. It crunched for a few moments, then when it told me I'd have to restart, I clicked 'OK' but did not restart. I went back and deselected that same box, went through the same dance (We're using Sharepoint 2007, so I can't leave the function on...) and I was suddenly able to continue my work without having to 'End Task' on Access like I usually would.

I remember finding code elsewhere to monitor the filesize of the virtual memory, so I have my trigger, but I was wondering if any of you had some thoughts on how to automate this purge once that trigger fires off.

 

[TesseractE]

So I have a partial solution... I've combined the VM filesize detection with vb that will generate a batch file to kill the MSACCESS process and reboot the client application. The filesize detection warning will not immediately reboot the client app, but will prompt the user with an OK/Cancel option.

VM filesize detection function:

Declare PtrSafe Sub GlobalMemoryStatus Lib "kernel32" (lpBuffer As MEMORYSTATUS)
Public Type MEMORYSTATUS
   dwLength As Long
   dwMemoryLoad As Long
   dwTotalPhys As Long
   dwAvailPhys As Long
   dwTotalPageFile As Long
   dwAvailPageFile As Long
   dwTotalVirtual As Long
   dwAvailVirtual As Long
End Type

 
Function ReturnVM() As Double
    Dim Mem As MEMORYSTATUS
    Dim Result As Integer
    Mem.dwLength = Len(Mem)
    GlobalMemoryStatus Mem
    ReturnVM = Format((Mem.dwTotalVirtual - Mem.dwAvailVirtual) / 1073741824, "0.000")
    
    Debug.Print ReturnVM
    
    If ReturnVM >= 1.425 Then
      Result = MsgBox("Windows Virtual Memory approaching limit.  Please restart the ClientApp.", vbOKCancel)
      If Result = vbOK Then
        os_Restart
      End If
    End If
End Function

Restart function:

Public Function os_Restart()
    Dim fso As Object
    Dim oFile As Object
    Dim BatchContents As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFile = fso.CreateTextFile("C:\Users\" & Environ("Username") & "\Desktop\RestartAccess.bat")
    
    BatchContents = "taskkill /f /im MSACCESS.EXE" & vbCrLf & _
                    "ping -n 5 127.0.0.1 > nul" & vbCrLf & _
                    "del " & Chr(34) & "C:\Users\" & Environ("Username") & "\Desktop\ClientApp.laccdb" & Chr(34) & vbCrLf & _
                    "start " & Chr(34) & Chr(34) & " " & Chr(34) & "C:\Users\" & Environ("Username") & "\Desktop\ClientApp.accdb" & Chr(34)
    
    oFile.WriteLine BatchContents
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing
    Call Shell("C:\users\" & Environ("Username") & "\Desktop\RestartAccess.bat")
End Function

Clean up the batch file - Placed in the Form_Open of the Form that's launched when the file opens:

If (FileExists("C:\Users\" & Environ("Username") & "\Desktop\RestartAccess.bat") = True) Then
     Kill "C:\Users\" & Environ("Username") & "\Desktop\RestartAccess.bat"
     Debug.Print "Restart File Killed."
  End If

I also tied in another method to trigger the restart using a Bug Tracking form, so if I'm called for support, I can direct them there rather than having to get them to CTRL-ALT-DEL, etc.

This makes life a LITTLE easier, but I'm still trying to find a way to clear everything out without forcing the user to restart the ClientApp so I can prevent this from happening entirely. Right now, this only makes the user experience a little less awful. ^^;