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.

A
Anthony Passeri, MBA
••6 min read
Access Runtime Limitations You Hit Before You Know It

Every tool has limits. Access is no exception. What makes Access limits particularly frustrating is that many of them are not documented prominently, do not produce clear error messages, and only become apparent after you have built something that depends on them not existing.

Here are the limits that catch Access developers off guard most often.

The 2 GB File Size Limit

The most well-known limit, but still the one that surprises people most when they actually hit it. An .accdb file cannot exceed 2 gigabytes. Period.

What makes this limit sneaky is how you approach it. If you are storing attachments or OLE objects in the database, you can reach 2 GB with far fewer records than you would expect. A database with 50,000 records and no attachments might be 50 MB. The same database with scanned documents attached to each record could be 5 GB — except it cannot be, because Access will stop accepting new records before you get there.

What to do: Move file attachments out of the database and store only file paths. Use Compact and Repair regularly. If you are approaching the limit with data alone, it is time to migrate to SQL Server.

255 Columns Per Table

Access tables are limited to 255 fields. This sounds like a lot until you inherit a database where someone decided to store survey responses as individual columns — one column per question, 200 questions, and now you need to add more.

Wide tables are also a symptom of a design problem. Data that varies by record (like survey responses or product attributes) should be stored in related tables, not as columns. But refactoring a wide table in a production database is painful.

What to do: Normalize the table design. Move repeating groups of columns into a child table with a foreign key. This is the right fix regardless of whether you are staying on Access or migrating.

32 Tables Per Query

Access queries can reference a maximum of 32 tables. This limit is almost never hit in well-designed databases, but it appears in databases that have grown organically over years — where someone kept adding JOINs to an existing query rather than redesigning it.

When you hit this limit, Access gives you a cryptic error about the query being too complex. The fix is to break the query into smaller pieces using intermediate queries or temporary tables.

64,000 Records in a Combo Box or List Box

If you populate a combo box or list box from a query that returns more than 64,000 rows, Access will truncate the list silently. Users will not see an error — they will just not be able to find the record they are looking for.

This limit is particularly insidious because it does not manifest as an error. Users just report that "the dropdown is missing some items" and you spend time debugging what looks like a data problem.

What to do: Filter combo box and list box queries aggressively. Use a search form instead of a dropdown for large datasets. If users need to select from thousands of records, a text search with a filtered list is a better UX anyway.

2,048 Characters in a Text Field

The Short Text field type in Access is limited to 255 characters. The Long Text (Memo) field type can store up to about 1 GB of text, but it has its own limitations: you cannot sort or group by a Long Text field, and it cannot be indexed.

The 255-character limit on Short Text catches people who use it for fields that occasionally need more — notes, descriptions, addresses in international formats. The fix is to change the field type to Long Text, but be aware of the sorting and indexing limitations.

10 Levels of Nested Subforms

Access supports subforms within subforms, but only up to 10 levels of nesting. In practice, more than 2 levels of nesting creates a user interface that is difficult to use and difficult to maintain. But if you have inherited a database with deeply nested subforms, this limit can cause unexpected behavior.

The 32,768 Character Limit on VBA Procedures

Individual VBA procedures (functions and subroutines) cannot exceed 32,768 characters. This is a lot of code for a single procedure, but it appears in databases where someone wrote a single massive procedure that handles an entire workflow.

When you hit this limit, the VBA editor refuses to save the module and gives you an error about the procedure being too long. The fix is to refactor the procedure into smaller, called procedures.

Network Timeout Limits

When Access is used over a network (shared drive), it has configurable timeout settings for record locking. The defaults are conservative — if a network operation takes longer than the timeout, Access throws a lock error.

On slow networks or with large operations, these timeouts can cause intermittent errors that are hard to reproduce. You can increase the timeouts in the Access Options, but increasing them is a workaround, not a fix. The underlying issue is that file-based database access over a network is inherently fragile.

The Real Limit: Concurrent Users

The most important limit is not documented as a number because it depends on your specific workload. Access is generally stable with 5–10 concurrent users on a fast local network. Beyond that, performance degrades and stability decreases.

This is not a hard limit — some Access databases run reliably with 20 users, and some fall apart with 5. It depends on query complexity, data volume, network speed, and usage patterns. But if you are planning to grow your user base, this is the limit that will matter most.

When Limits Become Migration Triggers

Hitting any of these limits is a signal worth paying attention to. Some of them — like the 255-column limit or the combo box row limit — can be worked around with design changes. Others — like the 2 GB file size limit and the concurrent user ceiling — are architectural constraints that cannot be engineered around.

When you find yourself repeatedly hitting Access limits, the question is not how to work around the next one. The question is whether the time spent on workarounds would be better spent on a migration to a platform that does not have these constraints.

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.

Hit an Access limit that is blocking your work? Schedule a free call and get an honest assessment of whether to fix it or migrate.

Schedule a Free Call →

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.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#Access limits#troubleshooting#performance#Access basics#migration

Keep reading

More on Access Basics