Access Database Performance: .accdb vs .mdb — What Actually Changes

One of the first questions people ask about migrating from .mdb to .accdb is whether it will make their database faster. The honest answer: maybe a little, in specific scenarios. But if your database is slow today, the format change alone won't fix it. Here's what actually changes under the hood, what gets faster, and where the real performance bottlenecks usually live.

What Changes at the Engine Level

The .mdb format uses the Jet 4.0 database engine, which was finalized around 2000 and never significantly updated. The .accdb format uses the ACE (Access Connectivity Engine), which is what ships with every modern version of Microsoft Office. ACE is a genuine rewrite of Jet with several meaningful improvements.

64-Bit Memory Addressing

ACE runs as a 64-bit process on 64-bit Windows with 64-bit Office. Jet 4.0 is 32-bit only. This means ACE can use more than 4GB of RAM for query processing, caching, and sort operations. For most Access databases this doesn't matter — they're small enough to fit comfortably in 32-bit address space. But for large tables (500,000+ records) doing complex aggregations, 64-bit addressing can reduce disk spill during sorts.

Multi-User Locking

Jet 4.0 uses page-level locking — it locks a 4KB page of data even when editing a single record, which causes unnecessary contention when multiple users edit different records on the same page. ACE improves this with an "Edited Record" locking mode that only holds locks during active edits. While both engines lock at page granularity internally, ACE's approach significantly reduces contention in practice. For multi-user shared databases (multiple people accessing the same .accdb on a network share), this is the most noticeable performance improvement: fewer "Record locked by another user" errors and faster concurrent access.

Improved Query Optimizer

ACE's query optimizer has been updated relative to Jet 4.0's. For complex queries with multiple JOINs or subqueries, ACE may choose better execution plans. The improvement isn't dramatic for simple queries, but for reports that aggregate large datasets across several tables, the optimizer improvements can reduce execution time noticeably.

What Doesn't Change

The 2GB database size limit is the same in both .mdb and .accdb. Both formats are file-based, single-file databases — they're not client-server architectures. Network performance characteristics are similar: both formats suffer when accessed over slow network links.

The fundamental bottlenecks in Access performance — poorly written queries, missing indexes, VBA loops that open record sets one record at a time, and oversized unlinked OLE objects — are present in both formats and need to be fixed regardless of migration.

Real Performance Improvements After Migration

Based on production migrations, here's where users consistently see performance gains:

Scenario	Expected Improvement	Why
Multi-user shared database (5+ users)	Significant	Edited-record locking reduces contention
Complex reports with 100K+ records	Moderate	Improved query optimizer + 64-bit memory
Large sort operations	Moderate	64-bit addressing reduces disk spill
Simple forms and CRUD operations	Minimal	Both engines equally fast for small datasets
VBA-driven row-by-row processing	None	Performance is in the VBA code, not the engine
Single-user desktop database	Minimal	Locking improvements irrelevant for one user

The Real Performance Killers (Format-Independent)

Before blaming the file format, check these common Access performance problems. They exist in .mdb and .accdb equally:

Missing Indexes

Every field used in a WHERE clause, JOIN condition, or ORDER BY should have an index. Without indexes, Access scans every record in a table for every query. A table with 50,000 records and no indexes on the filter fields will be slow in any format. Go to Database Tools → Analyze Performance to see indexing recommendations.

Unlinked OLE Objects

Embedding images or documents directly as OLE objects in Access tables was common in legacy databases but performs terribly. Each embedded OLE object stores not just the file but the full server application metadata. A table with 10,000 records each embedding a scanned PDF can be hundreds of megabytes and is painfully slow to browse. The fix: store files on the filesystem and save only the file path in Access.

VBA Recordset Loops

Code that opens a Recordset and processes records one at a time in a VBA loop is the most common source of "this takes 10 minutes to run" complaints. Replace these with set-based SQL operations wherever possible:

' Slow: VBA loop approach
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM Orders WHERE Status = 'Pending'")
Do While Not rs.EOF
    rs.Edit
    rs!ProcessedDate = Now()
    rs.Update
    rs.MoveNext
Loop

' Fast: SQL UPDATE
db.Execute "UPDATE Orders SET ProcessedDate = Now() WHERE Status = 'Pending'"

Uncompacted Databases

Both .mdb and .accdb accumulate internal fragmentation as records are added and deleted. A database that's grown from 200MB to 450MB over years of use may shrink back to 180MB after compaction, and queries will run faster on the compacted file. Run Database Tools → Compact and Repair Database regularly — or set the Compact on Close option in database properties.

After Migration: Performance Tuning Checklist

After converting to .accdb, run through these steps to maximize performance:

1. Run Database Tools → Analyze Performance and implement recommended indexes.
2. Compact the .accdb immediately after migration — the conversion process doesn't compact.
3. Search for VBA Recordset loops that can be replaced with SQL action queries.
4. Check for unlinked OLE fields storing documents or images — migrate those to filesystem paths.
5. Review any reports that take more than 5 seconds to open — add indexes or rewrite the underlying queries.
6. If multi-user performance is still poor, evaluate whether the data should move to SQL Server.

For the full migration walkthrough, see the Complete Guide to Access Database Migration. For validating that data is intact after migration, see the Access Data Integrity Verification guide.

← Back to all posts