Access VBA Compile Errors After .mdb to .accdb Migration — Complete Fix Guide

Your Access database converted cleanly — no obvious errors, tables intact, data all there. Then you open a form, click a button, and get hit with a VBA compile error. "Sub or Function not defined." "Compile error." The database that worked fine yesterday is suddenly broken.

This is one of the most common post-migration headaches, and it almost always has one of three causes: a broken VBA library reference, a name collision with a new built-in function, or a DAO/ADODB syntax change. All three are fixable without touching your business logic.

Step 1: Check for Broken References (the Most Common Cause)

Open the VBA editor with Alt+F11, then go to Tools → References. Scan the list for any entry that starts with the word MISSING. That's your culprit.

When Access converts an .mdb to .accdb, it preserves the list of referenced libraries — but it can't update the registered paths. If you also upgraded Office (from 2010 to 365, for example), the paths to those libraries have changed. The reference still points to the old location, which no longer exists, so VBA can't compile anything that depends on it.

Most commonly broken references after .mdb→.accdb migration

Reference	Why it breaks	Fix
Microsoft DAO 3.6 Object Library	Registered path differs across Office versions	Uncheck MISSING, re-check the current entry
Microsoft Access 16.0 Object Library	Version number in path is literal; 14.0 path no longer valid on 365	Uncheck the old version, check the current one
Microsoft Office 16.0 Object Library	Same version-number issue	Uncheck old, check current
Microsoft ActiveX Data Objects 2.x	ADODB version registered may differ from what's listed	Uncheck, re-check the matching installed version
Custom OCX / ActiveX controls	Control not registered on the machine, or .ocx file path changed	Re-register the OCX or remove/replace the control

The fix is straightforward: uncheck the MISSING entry, scroll the list to find the current equivalent, check that, and click OK. Then go to Debug → Compile to verify the reference fix resolved the errors.

Step 2: Compile Everything at Once

Don't wait for errors to surface one by one at runtime. After fixing any missing references, use Debug → Compile [YourProjectName] in the VBA editor. This compiles all modules simultaneously and stops at the first error it finds.

Fix that error, compile again, and repeat until the compile runs clean. One broken reference can cause dozens of apparent errors across multiple modules — fixing the reference and recompiling often clears most of them in a single step.

Step 3: Watch for Name Collisions with VBA Built-ins

Access 2007 and later added or promoted several functions to the VBA built-in library that didn't exist (or existed differently) in the older Jet-era Access. If your .mdb defined custom functions or subroutines with the same names, you now have a collision: VBA sees the name and doesn't know which version to call.

Functions that commonly collide with legacy custom names:

• Split — very commonly defined as a custom string utility in old .mdb code
• Join — same category
• Filter — frequently used as a wrapper function name
• Replace — often custom-implemented in Access 97/2000-era databases
• InStrRev, StrReverse — added in later VBA versions
• FormatCurrency, FormatNumber, FormatPercent — commonly wrapped
• MonthName, WeekdayName — date utilities that were DIY in older databases

The fix is simple: rename your custom functions to avoid the collision. For example, rename your custom Split to SplitCustom or SplitDelimited, update all call sites, and the conflict disappears. The built-in version is better anyway — use it where you can and remove the custom implementation entirely.

Step 4: DAO Syntax Changes Between Jet and ACE

Most DAO code that worked in .mdb works identically in .accdb. But there are a handful of patterns that behave differently under ACE (the .accdb engine).

Form references in SQL strings

In Jet (.mdb), you could reference open form controls directly in SQL passed to OpenRecordset:

' Works in .mdb, fails in .accdb
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders WHERE CustomerID = Forms!Form1!txtCustomerID")

ACE evaluates the SQL in a different context and can't resolve the form reference. The fix: pull the value into a variable first, then build the SQL string:

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM Orders WHERE CustomerID = " & Forms!Form1!txtCustomerID
Set rs = CurrentDb.OpenRecordset(strSQL)

Reserved word conflicts

ACE has a slightly expanded list of reserved words compared to Jet. Column names like Date, Name, Level, Value, Description, and Status are reserved in ACE. If your tables use these as field names and your SQL doesn't bracket them, you'll get syntax errors in .accdb that didn't exist in .mdb.

Fix: wrap reserved field names in square brackets in all your queries and SQL strings:

' Before
"SELECT Date, Name FROM Projects"

' After
"SELECT [Date], [Name] FROM Projects"

Step 5: Late Binding as a Diagnostic Tool

If you're not sure whether a compile error is caused by a broken reference or by a logic problem in the code itself, switch to late binding temporarily as a test:

' Early binding (requires valid reference)
Dim rs As DAO.Recordset
Dim db As DAO.Database

' Late binding (no reference needed — for testing only)
Dim rs As Object
Dim db As Object
Set db = CurrentDb

If the late-bound version runs correctly, the problem is your reference, not your code logic. Fix the reference, switch back to early binding (it's faster and gives you IntelliSense), and you're done.

When to Consider a Bigger Move

Most .mdb-to-.accdb VBA errors are fixable in an afternoon. But if your database is large, has dozens of modules, and is showing compile errors across the board, that's a sign of deeper architectural debt. The VBA was written for a runtime environment that no longer exists, and patching it one error at a time is a temporary fix to a permanent problem.

At that point, the real question is whether your Access application should be rebuilt from the ground up on a modern stack — one that doesn't depend on a 30-year-old desktop database engine, handles multiple users properly, and doesn't break every time Microsoft updates Office.

Quick Reference: VBA Error Diagnosis Checklist

1. Open VBA editor (Alt+F11) → Tools → References → look for MISSING entries
2. Uncheck MISSING references, re-check current versions
3. Run Debug → Compile to surface all errors at once
4. Check for custom function names that collide with VBA built-ins (Split, Join, Filter, Replace)
5. Look for SQL strings with form references — move values into variables first
6. Check for unbracketed reserved words in SQL (Date, Name, Level, Status)
7. Use late binding temporarily to confirm whether errors are reference-related or logic-related

Work through the list in order. In most migrations, steps 1-3 resolve 90% of the compile errors.

If you're still stuck after working through this checklist, the done-for-you service includes full VBA audit and repair — we fix the errors, you get a working database.

← Back to all posts