How to Audit Access VBA Code Before Migrating to .accdb

Access VBA is mostly forward-compatible across versions, but "mostly" isn't "completely." When moving from .mdb on 32-bit Jet to .accdb on 64-bit ACE, there are specific patterns in VBA code that will break on arrival — and they break silently until a user triggers the code path. This audit checklist finds them before migration day.

Step 1: Run the VBA Compile Check First

Before analyzing anything manually, let the compiler do its job. Open the VBA editor (Alt+F11), then go to Debug → Compile. This catches syntax errors, missing references, and type mismatches that exist right now in the .mdb. Fix all compile errors before proceeding with the migration analysis — you don't want to discover pre-existing code problems and migration problems at the same time.

Step 2: Find All Declare Statements (PtrSafe Issue)

The biggest VBA compatibility issue in 64-bit Office is Declare statements that call Windows API functions without the PtrSafe keyword. In 32-bit Office, this works fine. In 64-bit Office, it throws a compile error: "The code in this project must be updated for use on 64-bit systems."

Search for Declare Function and Declare Sub in the VBA editor using Edit → Find (Ctrl+F), searching all modules. Every Declare statement needs PtrSafe added:

' Old (32-bit only):
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

' Updated (works on both 32-bit and 64-bit):
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

For API functions that take pointer arguments (window handles, memory addresses), change Long to LongPtr for those specific parameters. LongPtr is 32-bit on 32-bit Office and 64-bit on 64-bit Office, so it's safe on both.

Step 3: Check Object Library References

In the VBA editor, go to Tools → References. Look for any reference marked "(MISSING)". A missing reference means Access can't find the object library (.dll or .tlb) that was registered when the code was written. This often happens when a database is moved to a new machine where the library isn't installed.

Common causes of missing references after migration:

• Different Office version (e.g., Office 2003 ADO reference on a machine with only Office 365)
• Third-party library not installed on the new machine
• ActiveX control OCX library that was never registered on this machine

For each missing reference, either install the library, update the reference to a newer equivalent, or rewrite the code to not require it. Microsoft DAO 3.6 is replaced by Microsoft Office 16.0 Access Database Engine Object Library in modern Access — update DAO references if they're pointing to the old version.

Step 4: Review DAO vs ADO Usage

Legacy .mdb databases sometimes use ADO (ActiveX Data Objects) for data access. ADO works fine in .accdb, but DAO is the native and preferred library for Access. If you have code mixing both, now is a good time to audit which is which — not necessarily to rewrite it, but to understand the patterns so you can troubleshoot faster if issues arise after migration.

DAO code typically looks like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Customers")

ADO code typically looks like:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Customers", cn

Both work in .accdb. The only action item here is to make sure both libraries are referenced (Tools → References) and neither is marked Missing.

Step 5: Check for Removed and Deprecated Functions

A handful of Access functions were removed or changed behavior across versions. Search your VBA code for these:

Function/Method	Status	Replacement
CurrentUser()	Returns empty string in .accdb (workgroup security removed)	Environ("USERNAME") for Windows user
DBEngine.Workspaces	Returns single workspace; workgroup features unavailable	Remove workgroup-specific code
SysCmd(acSysCmdGetObjectState, ...)	Works, but verify acSysCmdXxx constants are correct	No change needed if constants are correct
CreateWorkspace with user/password	Fails silently in .accdb	Remove or replace with connection-string auth
DoCmd.RunSQL with DDL	More restricted in sandbox mode	Use CurrentDb.Execute instead

Step 6: Audit Forms That Launch Other Office Applications

VBA code that creates Excel, Word, or Outlook objects via late binding (CreateObject("Excel.Application")) works fine in both .mdb and .accdb as long as those applications are installed. But code using early binding (referencing a specific version like "Microsoft Excel 14.0 Object Library") may break if the machine has a different Office version than when the code was written.

Convert early-bound Office automation code to late binding to avoid version dependency:

' Early binding (version-specific — can break):
Dim xl As Excel.Application
Set xl = New Excel.Application

' Late binding (version-independent — preferred):
Dim xl As Object
Set xl = CreateObject("Excel.Application")

Step 7: Test Every Button and Macro Trigger

After the migration, go through each form systematically and trigger every button, dropdown change, form open, and record save event. VBA issues often only surface at runtime. The compile check catches syntax errors, but logic errors and incorrect property references only appear when the code path executes.

Keep a log of each test:

• Form name and control tested
• Result: pass / error (with error number and description)
• Fix applied

This systematic approach is slower than random testing but ensures you don't ship the .accdb with a broken code path that only fires on the second Tuesday of the month when someone runs the monthly report.

For the full migration guide, see the Complete Guide to Access Database Migration. For help replacing ActiveX controls that the VBA code depends on, see the ActiveX Controls in Access guide.

← Back to all posts