How to Fix ‘System Resource Exceeded’ When Migrating to Windows 10
System Resource Exceeded Error Message
Lately we’ve been seeing a barrage of clients getting “System Resource Exceeded” when migrating to Windows 10, even though the system worked fine in prior versions of Windows. In some cases we’ve traced the issue to apps that use a lot of subforms in a tab control, so I’m going to describe a technique that will prevent the issue in those cases when each tab hosts a subform.
Tabs Make For Great UI – Until the memories run out
Just the other day I was working with a client’s app that was three tab levels deep: tabs within tabs, all of which were being loaded with subforms every time the form was opened. The app worked fine in Windows 7 but caused “System Resource Exceeded” error messages in Windows 10. The solution is to only load the subform when the user clicks on the tab and unload the subform when they click off to another tab.
Private Sub TabTasks_Change()
10 Static LastSubform As Access.SubForm
12 If Not LastSubform Is Nothing Then
14 If Len(LastSubform.SourceObject) Then
16 LastSubform.SourceObject = vbNullString
18 End If
20 End If
22 Select Case Me.TabTasks.Value
24 Case Me.Orders.PageIndex
25 If Me.frmOrders.SourceObject = vbNullString Then
26 Set LastSubform = Me.frmOrders
28 LastSubform.SourceObject = “frmOrder_sub”
30 End If
32 Case Me.Invoices.PageIndex
34 If Me.frmInvoices.SourceObject = vbNullString Then
36 Set LastSubform = Me.frmInvoices
38 LastSubform.SourceObject = “frmInvoices_sub”
40 End If
42 Case Me.Payments.PageIndex
44 If Me.frmPayments.SourceObject = vbNullString Then
46 Set LastSubform = Me.frmPayments
48 LastSubform.SourceObject = “frmPayments_sub”
50 End If
52 End Select
The magic happens on the tab’s Change event, which occurs when the user clicks on each tab of the control. In the code sample above I’m only interested in tabs that have subforms, in this case three tabs. (The technique is useless for tabs without subforms).
The static LastSubForm object, (line 10), keeps track of the last subform used and sets its SourceObject value to null in line 16. This will vacate the prior tab’s form from memory and keep memory use down to a minimum when the user switches off to another tab.
The select statement in line 22 is used to identify which tab was clicked on, then set the LastSubForm object to the subform contained in the tab and finally setting the SourceObject of the subform in line 28. Doing so will immediately load the subform into memory. The processed is repeated for the other two tabs of the control with subforms.
Keep In Mind
In order to minimize memory use you need to set the SourceObject property of each tab in question to an empty string in design view, otherwise all the subforms will load, which defeats the purpose of the technique.
The first tab of your control should load since it’s what your users will see when they open your form.
Just add more select statements as needed if you need to use this technique with more tabs, I’ve used it for a tab control that had 8 tabs.
If you have nested tabs you will need to repeat the approach for each tab control.
If your subform names are the same as your SourceObject property, you can further optimize the code by using a string variable in the code.
If you like this post share it!
Please share this post on LinkedIn, Twitter and Facebook, it will be much appreciated!
Join me on th enext Access with SQL Server meeting May 8th, at 6:30 PM CST, for more details please click here.
I am no way a Microsoft Access guru but I could really use the help on this. How can one apply this to be always applied with the fix listed above? Where do I enter and apply all of this code?
How does your solution change if Access is accessed via Citrix? Access is not listed among executable programs in Details tab of Task Manager. Thank you
Thanks so very much. This works like magic.
This worked for me, thank you!!
I am using Windows 10, so to find the affinity dialog you actually need to navigate the Task Manager to the “Details” tab, then right-click MSACCESS.EXE to “Set Affinity” and on my computer I left only CPU0, CPU1, CPU2, and CPU3 checked.
For Windows 10 go to the details tab before right-clicking.
For a while after my company switched to Windows 7, we were getting “System resource exceeded” errors when using or compacting Access files. The fix turned out to be (strangely enough) limiting the MSAccess.exe process to 4 or fewer processors.
The number of processors can be changed manually in Windows Task Manager. Go to the Processes tab, right-click on the MSAccess.exe process, click “Set Affinity…” and uncheck all but 4 processors. (I’m using Windows 7 still, so the steps might be different in Windows 10.)
You can also set the affinity programmatically like so:
CW Thank you so much! You really saved the day with this.
thank you so much this worked. been dealing with this for years!
CW – Have you possibly updated your code? I am having an issue with this code on a production product and getting an error code of zero. (hRet=0)