Moving Access VBA from 32-bit to 64-bit Office

For most of its life, Office was installed as a 32-bit program, even on 64-bit Windows. That has changed: 64-bit is now the default installation. Access applications written years ago are meeting 64-bit Office for the first time, and many of them will not run until the VBA is updated.

The error you will see

Opening the application, or compiling its code, produces a message along these lines:

The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.

The cause is Windows API declarations: the Declare statements that let VBA call functions in Windows itself. In 64-bit Office, memory addresses and window handles are 64 bits wide, and the old declarations describe them as 32-bit values.

PtrSafe is a promise, not a fix

The message suggests adding the PtrSafe keyword, and doing so makes the error go away. That is the trap. PtrSafe does not convert anything. It is a statement by the developer that the declaration has been reviewed and is correct for 64-bit.

If the keyword is added and nothing else is changed, the code compiles and then fails at run time, sometimes by closing Access without any message at all, because a 64-bit value is being squeezed into a 32-bit variable.

What actually has to change

Every parameter and return value that holds a handle or a pointer must become LongPtr. This type is 32 bits wide in 32-bit Office and 64 bits wide in 64-bit Office, so the same code works in both.

A typical 32-bit declaration:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

The same declaration, correct for both 32-bit and 64-bit:

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

The function returns a window handle, so the return type changes. The variables that receive that handle elsewhere in the code must change to LongPtr as well. That is the part most often missed, because those variables can be anywhere in the application.

Not every Long becomes a LongPtr. Values that are genuinely numbers, such as counts, lengths and flags, stay as Long. Deciding which is which means checking each declaration against the Windows documentation. A blanket search-and-replace gets it wrong.

Supporting old versions of Access

PtrSafe and LongPtr arrived with Office 2010. If the application must still run in anything older, the declarations have to be wrapped in conditional compilation:

#If VBA7 Then
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function GetActiveWindow Lib "user32" () As Long
#End If

If every user is on Office 2010 or later, the conditional block is unnecessary and the single PtrSafe version is cleaner.

Beyond the Declare statements

API declarations are the visible part. A proper 64-bit review also covers:

• ActiveX controls. Some older 32-bit controls have no 64-bit version. Forms that use them need an alternative.
• Compiled files. An ACCDE compiled in 32-bit Access will not open in 64-bit Access. It has to be rebuilt from the ACCDB source in each version you need to support.
• ODBC drivers and data sources. 32-bit and 64-bit drivers are separate, and so are the data source names defined for them. A connection that worked before may need to be set up again.
• Third-party libraries. Any DLL or add-in the application calls must exist in a 64-bit form.

Is 64-bit Access better?

For most Access applications, 64-bit brings no noticeable speed improvement. Its real advantage is the amount of memory it can address, which matters to very few databases. The reason to convert is practical: 64-bit is what new PCs are given, and other Office applications in the business may require it.

A sensible approach

1. List every Declare statement in the application. There are often fewer than expected, and some will turn out to be unused.
2. Replace API calls with built-in VBA or Access features where an equivalent now exists.
3. Correct what remains, including the variables that hold the results.
4. Compile and test in both 32-bit and 64-bit Access if both are still in use.