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

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.

Bravo!

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:

Option Compare Database
Option Explicit

' Code adapted from post by Dennis Wi in reply to "Access 2010 Compact & Repair drops indexes" post
' on Microsoft Office for Developers forums > Access for Developers forum
' http://social.msdn.microsoft.com/Forums/office/en-US/b188f2fe-8f5d-4a7b-b5f8-bc4c26854164/access-2010-compact-repair-drops-indexes?forum=accessdev

Declare Function GetProcessAffinityMask Lib "kernel32.dll" (ByVal hProcess As Long, ByRef dwProcessAffinityMask As Long, ByRef dwSystemAffinityMask As Long) As Boolean
Declare Function SetProcessAffinityMask Lib "kernel32.dll" (ByVal hProcess As Long, ByVal dwProcessAffinityMask As Long) As Boolean
Declare Function GetCurrentProcess Lib "kernel32.dll" () As Long
Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As SYSTEM_INFO)

Type SYSTEM_INFO
' http://www.java2s.com/Code/VBA-Excel-Access-Word/Windows-API/GettheProcessortypeandnumberofprocessors.htm

    dwOemID As Long
    dwPageSize As Long
    lpMinimumApplicationAddress As Long
    lpMaximumApplicationAddress As Long
    dwActiveProcessorMask As Long
    dwNumberOfProcessors As Long
    dwProcessorType As Long
    dwAllocationGranularity As Long
    dwReserved As Long

End Type ' SYSTEM_INFO

Public Function SetAffinity()
' Set processor affinity for this instance of MSACCESS.EXE.
' Useful for limiting Access to 4 CPUs on multicore Windows 7 computers
' to avoid "System resource exceeded" errors.

' Code adapted from post by Dennis Wi in reply to "Access 2010 Compact & Repair drops indexes" post
' on Microsoft Office for Developers forums > Access for Developers forum
' http://social.msdn.microsoft.com/Forums/office/en-US/b188f2fe-8f5d-4a7b-b5f8-bc4c26854164/access-2010-compact-repair-drops-indexes?forum=accessdev
On Error GoTo NecErrHnd

Dim PrintMe As String: PrintMe = "SetAffinity": Debug.Print PrintMe

    Dim TriggerNumProcessors As Integer, MaxAffinityMask As Integer
    TriggerNumProcessors = 4
    MaxAffinityMask = (2 ^ TriggerNumProcessors) - 1

    ' Only change affinity if computer has more than 4 cores.
    If GetNumProcessors > TriggerNumProcessors Then
        Dim hRet As Long
        Dim dwProcMask As Long
        Dim dwSysMask As Long

        hRet = GetProcessAffinityMask(GetCurrentProcess(), dwProcMask, dwSysMask)

        If dwProcMask > MaxAffinityMask Then
            ' Set affinity to the required number of processors.
            hRet = SetProcessAffinityMask(GetCurrentProcess(), MaxAffinityMask)

            If hRet  0 Then
                Debug.Print "Process affinity successfully set to " & MaxAffinityMask & "."
            Else
                Call MsgBox("Error : " & Err.LastDllError & vbCrLf & PrintMe, vbOKOnly + vbApplicationModal + vbMsgBoxSetForeground)
            End If
        End If
    End If

ExitMe:
    Exit Function

NecErrHnd:
    Dim ErrMsg As String: ErrMsg = PrintMe & ": Error #" & Err.Number & ": " & Err.Description
    Select Case Err.Number
        ' 2001: You canceled the previous operation.
        Case 2001
            Debug.Print ErrMsg
            GoTo ExitMe
        Case Else
            Debug.Print ErrMsg
            Call MsgBox(ErrMsg, vbOKOnly + vbApplicationModal + vbMsgBoxSetForeground)
    End Select

End Function ' SetAffinity

Function GetNumProcessors() As Integer
' Returns the number of processors on this machine.
' Based on code from http://www.java2s.com/Code/VBA-Excel-Access-Word/Windows-API/GettheProcessortypeandnumberofprocessors.htm

    Dim lpSysInfo As SYSTEM_INFO
    GetSystemInfo lpSysInfo
    GetNumProcessors = lpSysInfo.dwNumberOfProcessors

End Function ' GetNumProcessors