Access Keeps Crashing with Large Tables: What Is Happening and What to Do
When your Access database starts crashing, freezing, or throwing cryptic errors as your data grows, it is a signal worth paying attention to. Here is how to diagnose it and what your options are.
It starts subtly. A query that used to run in two seconds now takes thirty. A form that opened instantly now spins for a moment before loading. Then one day, Access just closes. No error message, no warning — it is simply gone.
If this sounds familiar, your database has probably grown past the point where Access can handle it comfortably. Here is what is actually happening.
The 2 GB Limit
The most well-known Access limitation is the 2 GB file size cap. An .accdb file cannot exceed 2 gigabytes. When you approach that limit, Access behavior becomes unpredictable — slow queries, failed saves, and outright crashes are all common symptoms.
What surprises many developers is how quickly you can approach 2 GB. Access stores OLE objects (embedded images, documents, attachments) inside the database file itself. A database with a few thousand records and a handful of attached PDFs can balloon to hundreds of megabytes quickly.
Run Compact and Repair first. Access databases accumulate dead space from deleted records and objects, and a neglected database can be 50% larger than it needs to be. After compacting, check the file size. If you are above 1.5 GB, you are in the danger zone.
Record Count Is Not the Real Problem
A common misconception is that Access has a hard limit on the number of records per table. It does not — the theoretical limit is around 255 million rows per table. The real constraints are more subtle.
Query performance degrades non-linearly. A table with 100,000 rows and no indexes on the fields you filter by will run dramatically slower than a properly indexed table with 1 million rows. Access uses Jet/ACE as its database engine, which supports indexes, but many Access databases are built without them.
Memory pressure. Access loads query results into memory on the local machine. If a query returns 500,000 rows and your machine has 4 GB of RAM shared with other applications, Access will struggle. This manifests as freezing rather than crashing, but the end result is the same.
Corruption risk increases with size. The larger an Access database, the more damage a single bad write can cause. Network interruptions, power failures, and even Windows updates that restart the machine mid-session can corrupt an Access file. Larger files take longer to repair and are more likely to have unrecoverable corruption.
Common Crash Scenarios
The query that never finishes. You run a report or a complex query and Access stops responding. This is almost always a missing index or a query that returns far more data than necessary. Check your query design — are you filtering early, or are you joining large tables and then filtering?
The form that crashes on open. If a form's record source is an unfiltered query against a large table, Access tries to load all matching records when the form opens. Add a WHERE clause to the form's record source, or open the form to a new record and let users search for what they need.
The "disk or network error" on save. This error appears when Access cannot write to the database file, often because the file is locked by another user, the network connection dropped momentarily, or the database is approaching the 2 GB limit.
Random crashes with no pattern. If Access closes without warning and you cannot reproduce it consistently, corruption is the most likely cause. Run Compact and Repair, then check the Windows Event Log for application errors around the time of the crash.
Stabilization Steps
Before considering a migration, try these in order:
-
Compact and Repair. Do this first, always. It resolves a surprising number of issues.
-
Add indexes. Open each large table in Design View. Add indexes on every field used in WHERE clauses, JOIN conditions, and ORDER BY clauses. This is the highest-impact change you can make.
-
Split the database. If you have not already separated your tables into a back-end file, do it now. The split architecture reduces the risk of corruption from network interruptions.
-
Move attachments out. If you are storing files inside the database using the Attachment field type or OLE objects, move them to a file server and store only the file path in Access. This alone can reduce file size by 80% in some databases.
-
Archive old data. If your tables contain years of historical data that nobody queries regularly, move it to an archive table or a separate database. Keeping active tables lean improves query performance significantly.
-
Increase the page timeout. In multi-user environments, increasing the record lock timeout can reduce the frequency of lock conflict errors that sometimes manifest as crashes.
When You Have Done Everything and It Still Crashes
There is a point where you have applied every optimization and the database is still unstable. At that point, the architecture is the problem, not the configuration.
Access was designed for databases up to a few hundred megabytes with a handful of concurrent users. If your database has grown beyond that — in size, in user count, or in complexity — you are running a production workload on a tool that was not designed for it.
The migration path depends on what you need:
- SQL Server is the right destination if you need to keep a desktop front end, have complex queries, or need robust transaction support.
- Azure SQL is SQL Server in the cloud — the right choice if your team is distributed or you want to eliminate on-premises infrastructure.
- Power Apps + Dataverse is the right destination if you want a modern web-based front end and your team is already in the Microsoft 365 ecosystem.
The data migration from Access to any of these platforms is well-understood and largely automatable. The front-end rebuild is the more significant investment — but it is also the investment that eliminates the crashes permanently.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Is your Access database crashing or corrupting with large tables? Schedule a free call and get an expert diagnosis.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Access Basics
Access Runtime Limitations You Hit Before You Know It
Microsoft Access has hard limits that are not obvious until you run into them. Here are the ones that catch developers off guard — and what to do when you hit them.
Why Is My Access Database So Slow with Multiple Users?
If your Access database crawls when two or more people are using it at the same time, you are not alone. Here is what is actually happening — and what your options are.
Managing Multi-User Conflicts in Access Databases
Record locking, split databases, and refresh strategies — here is what every Access developer needs to know about keeping shared databases stable under concurrent use.