Jet SQL Migration: What Changes When You Move from .mdb to .accdb

The jump from .mdb to .accdb is not just a file format change — it's an engine change. The .mdb format uses the Jet 4.0 database engine, which has been frozen since Office XP. The .accdb format uses the ACE (Access Connectivity Engine) database engine, which is what ships with modern Office and runs on 64-bit Windows. Most of your SQL migrates cleanly. Some of it breaks in ways that aren't obvious until a user clicks a button at 3pm on a Friday.

This guide covers every SQL compatibility issue we've seen when migrating production Access databases from .mdb to .accdb, including how to find them before they find you.

Why the Engine Change Matters

Jet 4.0 was permissive. It tolerated unquoted identifiers, ignored many reserved words used as field names, and quietly accepted syntax that would fail in any standard SQL database. ACE is stricter — not ANSI-strict, but stricter than Jet. It enforces reserved words more aggressively and has tighter parsing rules.

The good news: ACE is backward-compatible for the vast majority of Jet SQL. The bad news: the cases where it isn't are hard to spot in static code review. They only blow up at runtime when a specific query runs.

Reserved Words: The #1 Migration Issue

The most common SQL breakage after .mdb to .accdb migration is unbracketed reserved words used as field or table names. Jet 4.0 let you get away with naming a field Name, Date, Status, or Level and writing SQL that referenced those fields without brackets. ACE enforces those as reserved words and throws a syntax error.

Commonly Affected Field Names

Reserved Word	Common Use Case	Fix
Name	Person or company name field	[Name]
Date	Record date field	[Date]
Status	Order or record status	[Status]
Level	Permission or category level	[Level]
Order	Order number or sort order	[Order]
Group	Department or category grouping	[Group]
Month	Calendar month field	[Month]
Year	Fiscal or calendar year	[Year]
Type	Record type or category	[Type]
Value	Numeric or lookup value	[Value]

The fix is always the same: bracket the identifier. But finding every place a problematic field name appears across dozens of saved queries is the tedious part.

How to Find All Reserved Word Violations

Open the Access Query Analyzer (Query → SQL View) and look for field names that match any word in the ACE reserved word list. For large databases with many queries, run this VBA snippet in the Immediate Window:

Dim db As DAO.Database, qd As DAO.QueryDef
Set db = CurrentDb
For Each qd In db.QueryDefs
    Debug.Print qd.Name & ": " & qd.SQL
Next qd

Paste the output into a text editor and search for the common problem words listed above. Any occurrence that isn't already bracketed needs fixing.

Data Type Differences

Most data types map cleanly between Jet and ACE, but there are a few edge cases worth knowing:

Numeric Precision

Jet 4.0's Currency type maps to ACE's Currency without issue. However, some legacy databases use Single or Double fields to store financial values. ACE handles these the same way Jet does — but if you're running SUM or AVG aggregations on floating-point financial fields, you may see tiny rounding differences in the .accdb results. This is usually a pre-existing Jet bug surfacing, not an ACE regression.

Text Field Length

Jet 4.0 Short Text fields cap at 255 characters. ACE Short Text fields also cap at 255. No change there. If you have Memo fields in .mdb, they become Long Text fields in .accdb — functionally identical, just renamed.

AutoNumber Fields

AutoNumber fields with replication IDs (GUID type) behave differently in .accdb. If your .mdb uses GUID AutoNumbers (less common, but used in replicated databases), test those tables carefully after migration. Standard Long Integer AutoNumbers migrate without issue.

SQL Aggregate Functions

ACE supports all the same aggregate functions as Jet: COUNT, SUM, AVG, MIN, MAX, FIRST, LAST. One common source of confusion: the FIRST and LAST functions in Access SQL don't guarantee order — they return the first or last value the engine encounters, which may not match what you expect if the underlying table lacks a consistent sort. This is true in both Jet and ACE, so it's not a migration issue per se, but it's worth knowing if you're troubleshooting unexpected results after migration.

Subquery Behavior

Correlated subqueries generally work the same in ACE as in Jet. However, ACE is stricter about ambiguous column references in subqueries. If a subquery references a column that exists in both the inner and outer query without explicit table qualification, ACE may resolve it differently than Jet did — or throw an error where Jet silently picked one. Qualify all column references in subqueries to be safe:

-- Potentially ambiguous in ACE:
SELECT * FROM Orders WHERE OrderID IN
  (SELECT OrderID FROM OrderItems WHERE Quantity > 10)

-- Explicit table qualification (safer):
SELECT * FROM Orders WHERE Orders.OrderID IN
  (SELECT OrderItems.OrderID FROM OrderItems WHERE OrderItems.Quantity > 10)

DDL Differences

If your application creates or alters tables at runtime using DDL queries (CREATE TABLE, ALTER TABLE, DROP TABLE), check these carefully. ACE supports the same DDL syntax as Jet with one notable change: CREATE TABLE statements that specify CONSTRAINT inline are stricter about syntax in ACE. If you have DDL that ran silently on Jet despite minor syntax issues, it may fail explicitly on ACE.

Pass-Through Queries

Pass-through queries send SQL directly to a linked server (SQL Server, Oracle, etc.) and bypass the local engine entirely. These don't use Jet or ACE SQL — they use the target database's SQL dialect. Pass-through queries migrate to .accdb without changes and continue to work exactly as before. The only thing that changes is the file containing the connection string, which moves from .mdb to .accdb.

Linked Table SQL

Queries that join local Access tables with linked SQL Server tables work in both .mdb and .accdb. After migration, re-link the tables to refresh the connection (right-click the linked table in the Navigation Pane → Linked Table Manager). The SQL in queries that reference linked tables doesn't need to change.

Action Queries: INSERT, UPDATE, DELETE

Action queries follow the same reserved word rules as SELECT queries. If your UPDATE statement references a field named Status or Date without brackets, it will fail in .accdb. The reserved word issue applies to all query types equally.

One additional watch point: UPDATE queries that set a Memo / Long Text field to a value longer than 255 characters should work fine in ACE, but if your VBA code is building those UPDATE statements dynamically and truncating strings at 255 somewhere in the logic, the migration is a good time to catch that.

A Practical Pre-Migration SQL Audit

Before running the migration, do this 30-minute SQL audit:

1. Export all query SQL to a text file using the VBA snippet above.
2. Search for unbracketed reserved words: Name, Date, Status, Level, Order, Group, Month, Year, Type, Value.
3. Search for CONSTRAINT in DDL queries and verify syntax against ACE DDL spec.
4. Review any query that does correlated subqueries — add explicit table qualifiers.
5. Note any queries that use Memo field names — they'll work fine, but document the rename to Long Text for your team.

This audit takes less than an hour for databases with fewer than 100 queries. For larger databases, LegacyLeaps's free scan flags these issues automatically and tells you exactly which queries need attention before you convert.

For a complete walkthrough of the .mdb to .accdb migration process — including table structure, VBA, forms, and data validation — see our Complete Guide to Access Database Migration.

← Back to all posts