Access VBA Question

Mac M
Mac M used Ask the Experts™
on
How can I make this run in Windows 10 64bit, my users are receiving the System Exceed Resources for Access 2016???....The below worked for Windows 7 32bit but I'm unsure what need to be changed for 64bit.....

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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Well, where do you get that error? When do you get the error? What Access version exactly?
Chief Technology Officer
Commented:
There is so much information on Google about this I know you haven't considered doing your own research on this, so here goes:
If you are going from 32-bit Windows to 64-bit Windows, while keeping 32-bit Access/Office on the 64-bit machine, the answer is simple - putting "PtrSafe" in your api declarations:
Declare Function GetProcessAffinityMask Lib
changes to
Declare PtrSafe Function GetProcessAffinityMask Lib
If you need it to work in both 32-bit AND 64-bit Windows, then you'll need to use Conditional Compilation:
#If VBA7 Or Win64 Then
Declare PtrSafe Function GetProcessAffinityMask Lib 
#Else
Declare Function GetProcessAffinityMask Lib 
#End If

Open in new window


Try Google search and you'll find everything you need to know how to proceed.
Mac MSolutions Engineer

Author

Commented:
Thanks Mark,

I'm still learning the VB language and how to decipher it's many acronyms...You solution was what I needed in order to run the code in Access 2016....
Mac MSolutions Engineer

Author

Commented:
Thanks Mark,

I'm still learning the VB language and how to decipher it's many acronyms...You solution was what I needed in order to run the code in Access 2016....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial