Specs & Limits
By Richard Rost
3 hours ago
Specifications, Limits, and Real-World Performance In this lesson, we will review the specs and limits of Microsoft Access, covering key published limitations like the 2GB maximum database size, the number of objects, modules, indexes, fields, and users, as well as less common specifications such as enforced relationships and query limits. We will discuss which Microsoft Access specifications actually matter in real-world use, what factors can affect performance, the importance of good table design, and when certain limits may indicate it is time to consider alternatives like SQL Server. We will also mention recent findings that suggest some published limits may be guidelines rather than hard restrictions. Simon from Aurora, Colorado (a Platinum Member) asks: How big can a Microsoft Access database get before I have to worry about it? I've been using Access for years to keep track of customers and orders, and the database keeps growing. I keep hearing people say Access has limits, but nobody seems to agree on what those limits really are or when they become a problem. Links
Keywords TechHelp Access, Access specifications, Access limits, ACCDB file size, split database, frontend backend, compact and repair, normalization, short text field limit, concurrent users, wired network, enforced relationships, long text fields, query table limit, indexes, Unicode compression, database bloat
Subscribe to Specs & Limits
Get notifications when this page is updated
Intro In this lesson, we will review the specs and limits of Microsoft Access, covering key published limitations like the 2GB maximum database size, the number of objects, modules, indexes, fields, and users, as well as less common specifications such as enforced relationships and query limits. We will discuss which Microsoft Access specifications actually matter in real-world use, what factors can affect performance, the importance of good table design, and when certain limits may indicate it is time to consider alternatives like SQL Server. We will also mention recent findings that suggest some published limits may be guidelines rather than hard restrictions.Transcript Have you ever wondered whether Microsoft Access can really handle what your business is throwing at it? Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to talk about Microsoft Access, specifications and limitations, and how they compare to real-world use. We'll look at what Microsoft's official numbers say, where those limits actually matter, and what I've learned after building Access databases and teaching Access for more than 30 years.
Now I get questions like this all the time, but today's question comes from Simon in Aurora, Colorado, one of my platinum members. Simon says, How big can a Microsoft Access database get before I have to worry about it? I've been using Access for years to keep track of customers and orders and the database keeps growing. I keep hearing people say Access has limits, but nobody seems to agree on what those limits really are or when they become a problem.
Yeah, lots of people ask me all the time, how big can the database get, how many tables can I have, how many fields should a table have, how many users can work on the database, how big can the database get before I have to move to SQL Server.
So let's take a look at what Microsoft actually publishes on their official specifications page for Access. We'll look at some of the more interesting numbers on the page and we'll talk about what they mean in the real world. And then we'll talk about how some of these limits might actually not be the whole story.
All right, so here's Microsoft's official specifications page for Access. Here's the link up here. I'll put a link down below you can click on if you want to go read this for yourself. I'm not going to read every single item on this page. A lot of these are just technical details that you'll probably never run into, but I do want to focus on the ones that people actually ask me about.
Let's start with the big one. The maximum size of an Access database is two gigabytes. That's for a specific Access database file. And this is the one that the Access critics love to repeat. Oh, Access is limited to only two gigabytes. Yeah, that's technically true. A single ACCDB or MDB file can only be two gigabytes. But what they leave out is that you can link tables together from other databases.
So if one backend gets close to two gigabytes, you can split things out into multiple backend files. So yes, there's a two gigabyte limit per file, but that's not necessarily two gigabytes for your entire application.
Now it would be very rare for you to build a frontend database that is anywhere near two gigabytes. I've got one that's huge. I've been building it for 20 years and it's like 800 megabytes. As far as your backends go, yeah, I've got databases where they're split into multiple backends. I've got customers in one, orders in another, order details in another one. So yeah, you can stretch that out over multiple gigabytes if you have your data spaced out across multiple files like that.
Of course, you want to make sure you're not storing images and OLE objects and attachments and stuff like that in your database. That is not the way we do things. Objects like that should not be stored inside your database.
So make sure you compact your database regularly because if you add and delete a lot of records that can also bloat the database. If you add a thousand records and delete 500 of them, you don't get all that space back until you compact and repair. So make sure you're doing that like once a week or so.
You can split your database so your frontend is separate from your backend. You can have multiple backend files and remember every user gets their own frontend copy.
We're not going to store images and attachments and OLE objects and other big things like that in our database file. Those cause unnecessary bloat. Access was not designed for it. Watch this video for what you should be doing.
The next interesting thing on this list: total number of objects in a database, 32,000. That's tables, queries, forms, reports, macros, modules, all that stuff combined. If you hit this limit, I'd honestly like to see your database because I would have questions. I've built big, big databases before. I'm talking maybe four or five hundred objects, but thousands? Come on. What are you doing?
Number of modules: a thousand. We're going to come back to this one later because it turns out there might be more to that story, but even then you shouldn't hit anywhere near a thousand.
There's an object name, like a form name. Yeah, okay, 64. I think 20 is more than enough. You want to keep your objects named so that you know what they are, like customers with orders that are past due dot whatever, you know, query, almost dot com. You want to keep them named so that you know what they are, but don't make them exceedingly long.
I try to, for the purposes of searching in the navigation pane, include, for queries specifically, what other tables they're based on. So if it's customers and orders in there, I make sure that customer and order appear in that name somehow so that if I type in order, I can see all the queries and forms and stuff that are related to orders. That's how I do it.
Number of characters in a password and a user group or username, that's going way back to when Access actually had user-level security in it, which it doesn't anymore.
This one makes me laugh. 255 concurrent users. Nowhere near that. In my experience, if you've got 20 or 30 people hammering away on an Access backend all day long, it's probably time to start thinking about SQL Server. 20 to 30 users is the sweet spot.
Can Access handle more than that? Yes. I've built databases in the past for companies that had upwards of 50 users working on an Access database, but they all weren't hammering at it all day long. We're not talking about a call center where people are reading, writing, adding data all the time. We're talking about maybe the guy out in the warehouse who looks up a product number once in a while. Or the receptionist who occasionally has to pull up a customer record. They're not solid.
So it's really not like there is one number that works. It depends on your database. It depends on how it's set up. It depends on what you're doing with it, and who's doing what. So 20 to 30 users is like the sweet spot.
Of course, that assumes you've got a nice, quality wired network. Can you use it over wireless? Yeah, you can, but I don't recommend it. People push back on me all the time when I say that online, like, oh, yeah, I can use Access wireless all the time. Yeah, you can. If your wireless signal is perfect, you don't have any drops. If you're reading and writing records and all of a sudden your wireless drops, then you're going to corrupt your database. So wired networks are preferred.
There's a lot more to it than just how many users you can have. 255 is the theoretical maximum. 20 to 30 is usually the sweet spot.
Moving down to tables. We've got characters in a table name, 64, sure. Fine. Characters in a field name, fine.
Number of fields in a table, 255. Sorry, the search keeps popping up. I'm using Edge for this one. This is one of those limits where if you're getting anywhere close to it, the problem isn't Access. The problem is probably your table design. You want to look at normalization.
If you've got fields in your table called item1, item2, item3, item4, item200, that's usually a sign that normalization might be a good idea. You've probably got too much stuff in one table. That's a whole separate video. If you have 16 phone numbers and 85 addresses, those should all be in separate tables.
There's no way. I've got some huge databases. I think one has maybe 50 fields in it because it was demographic information. So yeah, 255 is really stretching it.
Number of open tables, you should never have thousands of open tables at the same time. Available connections is the same.
Total size goes back to that two gigabyte. Two gigabytes is the limit for a single table because obviously it's the limit for a single database file. If you've got a table, like let's say an orders table and you've got 20 years worth of data in it, and it's starting to approach two gigabytes, you might want to start breaking that off into an orders table and then an orders archive table. You probably don't look up those orders from 15 years ago all the time.
Take everything that's more than five years old or whatever works for your business and just shove that off to an archive table. If you need all of that together, you can either union query it later on for one customer or you can have a search that searches two tables. There are lots of ways around that two gigabyte limit. And yeah, the best way is SQL Server, but you don't need it. There are ways around it.
Here are some interesting ones. 255 is the max for short text fields. That's pretty much standard in most databases. Long text fields, formerly called memo fields, 65K. That's 65,000 characters when entering data through the user interface. That means you're typing it into a form or a table field. It's one gigabyte of character storage when entering data programmatically. That's a lot. You could store several copies of War and Peace in a single field if you wanted to, not that I'd recommend it.
Programmatically means you're importing data or you're generating it with VBA or something along those lines. But 65K is when you enter it through a form. If you have more than 65K in a field and you try to edit it, it's going to truncate it, so be careful with that.
Size of an OLE object. We don't use OLE objects, so don't do that.
What do we have here? Indexes in a table: 32. You shouldn't come anywhere near 32 indexes in it. If you've got 50, 60 fields and you're searching on lots of them, sorting on lots of them, okay, just take it easy with those. If you've got more than a dozen or two, ask yourself if they're really necessary. Remember, indexes are great. They speed up searches and sorts, but they slow down updates too. So, if you're really not searching on all those fields, you might want to reconsider some of those indexes. Every time you add a record, Access has to rebuild every one of those indexes. So don't over-index your fields.
Validation message, primary keys are all kind of silly.
There's one of the stranger limits on the page. Microsoft says a record can contain up to 4,000 characters when Unicode compression is enabled.
Unicode compression. What is Unicode? Well, it's the character standard that allows Access to store text from just about every language in the world. Not just regular English letters, but things like accented characters, Umlauts, Greek, Russian, Chinese, Japanese, thousands of other symbols in the character sets.
Because Unicode has to support all of those different languages, those characters generally take up more storage than just plain English text, stuff on the keyboard. That's where Unicode compression comes in.
So years ago, Microsoft added a feature that lets Access store ordinary English text more efficiently. If you're mostly storing regular letters, numbers, and punctuation, Access can compress that data behind the scenes and save space. Think of it like vacuum packing your clothes before a trip.
I just did that. I love those little bags. I didn't get the one with the little vacuum that you hook up to, but I got the one where it's got like a valve and you pack your stuff in the big bag and then you put it down on the bed and then you lay on it and your body weight just smushes all the air out. I love those. I've got like 5 or 6 of them. They're wonderful.
Anyway, you still got the same clothes, but they just take up less room in the suitcase. It just makes the suitcase easier to close and zipper. It's still the same stuff inside of it. Half the time they fluff back up inside when I open up the suitcase, they've all regained their size. But at least I got it closed and I got it through the airport check in.
But anyway, the 4,000-character limit, that's basically just telling you how much text Access can pack into a single record before it starts running into internal storage limits.
Now before anybody panics, remember this only applies to regular short text fields. Long text fields are handled differently and don't count toward this limit. Honestly, if you've got so many short text fields that you're worried about fitting 4,000 characters into a single record, you've probably, like I said earlier, got some table design issues and normalization that you want to worry about as well.
Down to queries: maximum number of tables in a query is 32. Every once in a while somebody posts a query in the forum, they post a screenshot and it looks like somebody spilled spaghetti all over the query designer. There are lines going everywhere. You've seen them.
If you've got 32 tables in one query, it's time to simplify things a little bit. Five, ten, maybe make a separate subquery because you can nest up to 50 of them. You shouldn't, but you can. Don't put so many tables. That's one of the things that people have problems with when they're designing queries too, is they try to make one query do too much work. Get the data that you need. Do it in stages. We've got customers and orders. Here's customers, here's their orders. Now we want to see ones that are 30 days old. I put a criterion on it. Now we've got to bring in order details, so make another query and bring in what you need, instead of trying to just jam pack everything into one query.
Yes, I realize I skipped one. My eye just got caught on this one. Number of enforced relationships: notice Microsoft doesn't say relationships. They specifically say enforced relationships. What's the difference? Well, an enforced relationship is one where Access is actively making sure your data stays consistent.
For example, you've got a customers table and an orders table. Access can enforce a rule that says every order must belong to a valid customer. Referential integrity. If somebody tries to enter an order for a customer that doesn't exist, Access stops them. That's an enforced relationship.
Microsoft doesn't give specific numbers here because the limit depends on something else: indexes. Every enforced relationship uses indexes behind the scenes to keep track of those connections between the tables. So the more indexes you already have on a table, the fewer enforced relationships you can create.
In practical terms, this is almost never something you're going to actually run into. A typical business database might have a few dozen relationships, sometimes even hundreds in a large application. That's perfectly fine. I've been building databases for over 30 years and I've never had a project where I thought, wow, I'm running out of relationships. So while there technically is a limit, the real lesson is that Access can handle far more table relationships than most databases will ever need.
Remember, when you split your database into multiple backend files, you can't enforce relationships between them anyway. You have to do that in your code. That's a whole separate video.
So we skipped that one. Number of joins in a query: 16. That's ad hoc joins. Again, it should be plenty. If you're getting close to that number, you might want to step back and ask yourself whether you're building a query or a science experiment.
Recordset size is fine. Number of fields in a recordset. Sort limit. You can only sort by 255 characters in one or more fields.
Nested queries, like I said earlier, is 50. If you're 50 levels deep in nested queries, you don't have a query anymore. You have an archaeological site.
The rest of these are fine. Number of AND operators in a WHERE or HAVING clause: 99. If you've got 99 ANDs, I've got 99 ANDs in a query. One, I don't know.
Number of characters in an SQL statement. I actually hit this once. I don't know if it was 64,000, but I ran into a limit. I was generating SQL dynamically in VBA with lots of tables and lots of fields. I ran into, I forget, this is years ago. So I have actually run into this limit. Again, that was a matter of, I probably could have optimized better instead of trying to just loop. So my brain thinks in loop, so I was looping through, trying to get, I was trying to get tricky when I should have gotten smart. But again, you'll probably never hit it.
Forms and reports. Maximum number of characters in a label. Yeah, I've gotten close to that one because sometimes what I'll do is put instructions for the user in a big label on the side of the form. Like, make sure you do this first, do that, and that's better. So, yeah, I've made some pretty big labels. What do you do? You just make multiple labels. Then you take your label, you copy and paste it, and you make a second label. It's not a big deal.
Maximum form or report width is 22 inches. This one is actually interesting because Microsoft has been working on increasing some of these display limitations. I think it's either in beta or it's coming out to beta very soon. They're doing wide-format monitors, which I love because I've got a giant monitor on my screen. I've got little teeny tiny forms, and I want to make them bigger. Soon we will be able to, which again is proof that Microsoft is still investing in Access and they're still developing for it and adding new features. Huzzah!
Back when Access was developed and these limitations were made, nobody had a monitor that was 22 inches across. I remember when 14 inch monitors were the standard. If you had a 15 inch monitor, you were somebody important. 17 or 19 inch monitors, well, that's CEO level material right there. I think my monitor now is 50 some inches.
Maximum number of levels of nested forms or reports is seven. Seven levels deep is not a form anymore, that's a family tree. So, what do you have seven nested subforms for? No. Three or four maybe. One, come on.
Fields or expressions you can sort or group on in a report: ten. That's a reasonable ten grouping levels in a report. Number of headers and footers in a report: ten again. One page header, one report header and footer, and you get ten group headers or footers.
Here's my favorite weird one: the number of controls and sections you can add over the lifetime of a form or report. Notice the wording: not currently on the form, it's over the lifetime of the form. In other words, if you repeatedly add controls, delete controls, add controls, delete them, redesign the form, keep making changes over the years. Access keeps track of all that internally. It's a very strange limit. It's almost like autonumbers. Once you use them, they're gone. You don't get them back. Once you create a new control, it's gone. It's a strange limit.
We'll come back to this one in a minute too because it's not always the case.
Oh, and I skipped this guy here: the maximum number of pages, printed pages in a report, 65,000. If your report is 65,000 pages long, I don't think Access is your biggest problem. Your cost for toner is. So that's a lot.
What do we have here? Macros: number of actions in a macro. Honestly, I don't use macros that much to worry about this one. If you have thousands of actions in a macro, I almost said Action Jackson. Remember that movie? If you have thousands of actions in a macro, it's time to switch to VBA code.
The only time I really use macros is when you can't do something in VBA. Like checking to see if the database is in a trusted folder. I did a video on this one. VBA won't run if the database isn't in a trusted location, and you can't check to see if you're in a trusted location unless you use a macro. So that's one thing that I do use in an autoexec macro.
These are what Microsoft lists as the Access specifications. I've given you my thoughts on it. Some of these limits are things you'll never hit. If you do hit them, it's often a sign that your design needs another look. The 2GB limit is real, but it's easy to get around. The SQL statement limit is real. The user limit matters. Most of the others, they're so large that they rarely become practical concerns. The bigger challenge is usually proper database design, not database limits.
I'll be honest, back before I really knew what I was doing, I had a lot of problems with some of this stuff too. Putting too many fields in a table. Putting too many tables in a database. Things like that. So, if you've got tables that are orders 1996, orders 1997, orders 1998, you're going to run into problems by the year 2250.
Now, here's where things get interesting. I want to talk about ILA dogs for a minute.
Microsoft Access MVP Colin Riddington did a series of tests on many of these published limits and found that some of them may not actually be hard limits at all.
According to Colin's testing, he was able to exceed the published limit for database objects, the published limit for code modules, controls on forms, and report pages.
I'm not saying Microsoft is wrong and I'm not saying Colin is right. I have not verified these tests myself. But what I am saying is that his testing suggests that some of these published specifications may just be guidelines, outdated numbers, or limits that have changed over time because Access has been growing and evolving for 30 years. I'll put a link to Colin's article down below if you'd like to read the details and see his testing for yourself.
Whenever I'm putting together a video on any topic like this, I always do my research first and this page came up. So I wanted to mention it, and Colin has done a lot of great stuff in the Access community. So check it out. There's lots of stuff on this site and he's got a lot of good stuff on here. So, check him out.
So, today we learned that Microsoft's published Access specifications and limitations are useful guidelines, but most databases will never come anywhere close to those limits in the real world. Some limits are hard limits. Some have changed over time. If you're getting near most of them, it's probably time to take a look at your database design. Are you building a Franken-database or do you have properly normalized tables and decent fields and all that good stuff?
Post a comment down below if you have ever run into one of these limits or how big your Access database has grown. I'm curious what you guys are dealing with.
But in any event, that's going to be your TechHelp video for today brought to you by AccessLearningZone.com. I hope you learned something.
Live long and prosper my friends. I'll see you next time.Quiz Q1. What is the official maximum size of a single Microsoft Access database file?
A. 2 gigabytes
B. 5 gigabytes
C. 500 megabytes
D. 10 gigabytes
Q2. What can be done when an Access backend database is approaching the 2GB limit?
A. Split the data into multiple backend files
B. Store all data in one table
C. Upgrade your RAM
D. Change database file format to .xlsx
Q3. What should you avoid storing directly in your Access database to prevent unnecessary bloat?
A. Images and OLE objects
B. Customer names
C. Short text fields
D. Numeric fields
Q4. What is the recommended maximum number of active users for an Access backend according to real-world experience?
A. 20 to 30 users
B. 2 to 5 users
C. 100 to 200 users
D. 254 users
Q5. If you have nearly 255 fields in a single table, what is the likely issue?
A. Poor table design and lack of normalization
B. Not enough storage space
C. Too many users
D. Outdated Access version
Q6. What is the maximum number of objects allowed in an Access database according to Microsoft documentation?
A. 32,000
B. 1,000
C. 100
D. 254
Q7. How can you avoid Access file bloat after deleting many records?
A. Regularly compact and repair the database
B. Add new tables frequently
C. Switch to storing data in Excel
D. Make backup copies every minute
Q8. What type of network is recommended for multiple users working on an Access backend?
A. Wired network
B. Wireless network only
C. Cellular network
D. Satellite network
Q9. What is the maximum number of fields allowed in a single Access table?
A. 255
B. 1,000
C. 10,000
D. 128
Q10. If your order table's data is reaching the 2GB file limit, what is one practical suggestion given in the video?
A. Archive old data into a separate table
B. Buy a new server
C. Delete all old orders
D. Store more data in the frontend
Q11. What is the typical maximum length for Short Text fields in Access?
A. 255 characters
B. 65,000 characters
C. 1024 characters
D. 32 characters
Q12. Which field type allows up to 1GB of character data when used programmatically?
A. Long Text (Memo)
B. Short Text
C. Number
D. Date/Time
Q13. How many indexes can you have on a single Access table?
A. 32
B. 10
C. 255
D. No limit
Q14. What is the maximum number of tables you can use in a single query in Access?
A. 32
B. 2
C. 100
D. 8
Q15. What is an enforced relationship in Access?
A. A relationship where Access enforces referential integrity between tables
B. A relationship only shown in diagrams
C. A connection made in Excel
D. A duplicate record in a table
Q16. According to the video, what is a likely sign if you are approaching the published Access database limits?
A. Poor database design or lack of normalization
B. Fast wireless network usage
C. Recent Windows update
D. Excess user training
Q17. How should you name queries for easier searching in the navigation pane?
A. Include references to the tables involved
B. Use numbers only
C. Name all queries 'Query'
D. Use random characters
Q18. What happens if you reach the limit for controls and sections on a form "over its lifetime"?
A. You might not be able to add more controls, even after deleting old ones
B. Access will automatically reset the limit
C. Only VBA modules are affected
D. Reports will show blank pages
Q19. What does Unicode compression do in Access?
A. Compresses regular English character data to save space
B. Encrypts all data
C. Makes databases open slower
D. Doubles file size
Q20. What did Access MVP Colin Riddington find about some of Microsoft's published limits?
A. Some limits can actually be exceeded in practice
B. All published limits are hard limits
C. Limits are lower in new versions
D. There are no limits in Access
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone is all about Microsoft Access specifications and limitations, and how those official numbers from Microsoft line up with what you'll encounter when really building and using databases for your business. I have spent over 30 years building Access databases and teaching people how to use them, so I get questions on these limitations quite often.
One of the most common questions is how large an Access database can become before it starts causing trouble. People often hear about various limits, but there is a lot of confusion about which ones actually matter, and at what point they become important.
Let's start with the big one: the maximum database size. Microsoft officially states that an Access database file (like ACCDB or MDB) can grow up to two gigabytes in size. Critics point to this as a major weakness, but it is important to clarify that this limit applies to a single file. You have the ability to split your data across multiple backend files and link them together, so your overall application can easily exceed two gigabytes if you set it up that way. For example, you might keep customers in one backend, orders in a second, and order details in a third.
It is also uncommon for a frontend database to come anywhere near that two gigabyte limit. Even my largest frontend, built over a period of 20 years, is only around 800 megabytes. Backend databases can grow larger, but by splitting data logically and keeping each backend separate, you can work far beyond the official limit.
One important tip: do not store images, OLE objects, or attachments directly inside the database. These eat up space and add unnecessary bloat. The proper approach is to keep large files like images outside the database and store only a reference or path.
Regular maintenance is also essential. As you add and delete data, the file can get bloated. Compacting and repairing your database on a regular schedule, say once a week, is a good practice to recover space and keep things running smoothly.
Make sure to properly split your application so that each user gets his or her own frontend copy while sharing a backend (or multiple backends). This setup helps with both performance and reliability.
Now, let's look at some other official limits from the Microsoft specifications for Access. For example, the maximum total number of objects, such as tables, queries, forms, reports, macros, and modules, is 32,000. Frankly, you will probably never come close to this in a real-world database. Even large business applications that I've built rarely have more than a few hundred objects.
There is also a limit of 1,000 modules, which again is way more than most developers will ever need.
Object names, like table or form names, can contain up to 64 characters. I recommend shorter names for clarity and easier searching in the navigation pane. Including key table names in your object names, especially for queries, helps organize your work and makes it easier to find things later.
User-level security and password limits are mentioned in the old specs, but Access no longer supports user-level security like it did in older versions, so you can ignore these specifics.
Access documentation lists a maximum of 255 concurrent users. In practice, however, I have found that 20 to 30 users is the sweet spot for reliably sharing data using an Access backend file. Yes, you can exceed this, but only if those users are not all active at the same time. For example, if most people are just looking up information occasionally, you might get by with more users. If you have a large, active team regularly adding and updating data, you'll likely want to consider moving to SQL Server as your backend.
A solid, wired network connection is also key. Wireless networks can work, but they are not ideal. Any network drops can lead to corruption of your database, so stick to wired connections whenever possible.
As for tables, you can have up to 255 fields in a table. If you are nearing that limit, the issue is probably with your table design rather than Access itself. Overly wide tables often indicate the need for normalization. For instance, if you see fields named item1 through item200, or multiple phone and address fields, you are overdue for a redesign. Split those out into related tables.
A table can also use up to two gigabytes, which matches the overall file limit. If you have a huge table with decades of order history, consider archiving older records to another table. You can always reconnect them with queries if you need to access all records together.
Short Text fields can store up to 255 characters, which is typical for most databases. Long Text fields, which used to be called Memo fields, can store up to 65,000 characters when entered through the user interface and up to a gigabyte programmatically through code. Be aware that if the field grows beyond 65,000 characters, editing it directly can lead to truncation.
On the topic of indexes, you can have up to 32 indexes on one table. While indexes are important for performance when searching or sorting, over-indexing can slow down data entry operations, as Access must update every index with each new record.
There is an internal limit to how much text can fit into a record with Unicode compression enabled, which is about 4,000 characters. This usually only comes up if you have a large number of Short Text fields filled to capacity.
When it comes to relationships, Access supports many enforced relationships, with the limitation tied to the number of indexes you have. Enforced relationships are important for things like referential integrity, where for example, every order must have a valid customer. In practice, most databases never hit this limit.
If your database is split into multiple backend files, enforced relationships cannot span those files. You'll need to enforce those rules in your code instead.
As for queries, you can include up to 32 tables. Honestly, if you reach this limit, your query may be too complex. It is often better to build intermediate queries (sometimes called subqueries), focusing on smaller data sets and combining them as needed. You can nest up to 50 queries inside each other, although you should avoid this kind of extreme nesting in day-to-day work.
Access allows up to 16 joins in a single query, which should be plenty for almost any scenario.
There is also a maximum size for an SQL statement, which is over 64,000 characters. I have run into this once in my own work, usually the result of dynamically generating a huge SQL string in VBA. Nearly always, this kind of situation can be solved with better design.
For forms and reports, there is a width limit of 22 inches. With larger modern monitors, Microsoft is planning to expand this, which is a sign that Access continues to be updated and developed.
You can have up to seven nested forms or subforms, but if you design is becoming that complex, you should probably rethink your approach. Most applications never need to go more than a few levels deep.
Reports support up to ten group headers or footers, which should allow for complex formatting and output needs.
One particularly odd limit is the number of controls and sections you can ever add to a form or report over its entire history. Adding and deleting controls over time consumes these slots, and once you've used them, you do not get them back. This is kind of like an autonumber field. If you think you may have reached the limit, you may need to rebuild the form to recover space.
When it comes to macros, I rarely write huge macros. If your macro execution list grows into the thousands of actions, it is probably time to switch to VBA code, which is far more flexible and powerful for complex situations. One useful case for macros is to check whether the database is in a trusted folder, since VBA cannot run if the file is untrusted.
It is worth noting that some of Microsoft's published limits might actually be conservative. Colin Riddington, an Access MVP, has done extensive testing and found that in many cases these limits can be exceeded. Sometimes the specifications are just guidelines or leftover numbers from earlier versions. Access has evolved over decades, so it is not surprising that some hard limits have shifted. If you are interested in the technical nitty-gritty, Colin's research is available online.
The main takeaway is that, for most databases, these official limitations will never come into play. The real challenge is almost always around good table design, proper normalization, and solid architecture, not raw size or numbers of objects.
If you have ever run into one of these limits, or have stories about the size of your own Access database, I would be interested in hearing about your experiences.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Maximum size of an Access database file
Splitting databases into multiple backends
Storing images and attachments in Access
Importance of compact and repair
Splitting frontend and backend databases
Access concurrent user limits
Table design and normalization
Number of fields in a table
Strategies for handling large tables
Short text vs long text field limits
Index limits and best practices
Effect of Unicode compression
Maximum number of tables in a query
Enforced relationships vs standard relationships
Maximum joins in a query
Maximum sort characters in queries
Nested queries and recommended practices
Form and report width limitations
Maximum nested forms and reports
Grouping and sorting limits in reports
Lifetime control and section limits on forms
Macros and VBA in Access
Practical significance of published Access limitations
Differences between published and tested limitsArticle Have you ever wondered if Microsoft Access is really up to the challenge of handling your growing business data? Many people hear about Access limitations and worry that it may not be able to keep up as the years go by. I have been working with Access for over 30 years, and in this tutorial, I am going to walk you through what those limits actually are, what they mean in practice, and how you can work within them for most business needs.
When people ask about Access limitations, the most common questions are about how big an Access database can get, how many tables and fields are allowed, how many users can work at once, and when you might need to consider switching to SQL Server for your backend. Microsoft publishes official specifications for Access, but not all of these are hard limits that you will actually run into, especially with good database design.
Let us start with the most quoted limit: the size of an Access file. Your database file (the ACCDB or MDB file) can be up to two gigabytes. Critics of Access love to point out this limitation, but there is more to the story. That two gigabyte limit applies to a single file. You can split your data across multiple backend database files and link the tables together in your frontend. For example, you might keep customers in one backend and orders in another. This way, you can go well beyond the two gigabyte limit for your overall application. Also, most frontend files do not get anywhere near this size since they mainly store forms, queries, and code, not data.
It is important to avoid storing things like images, OLE objects, or attachments inside your database. Embedding large objects like these is the fastest way to bloat your file and run into limits. Instead, store images and documents externally and just keep the file paths in your database.
You should also compact and repair your Access database regularly. Access does not reclaim disk space for records that you have deleted until you run the compact and repair command, so if you add and delete large amounts of data, make it a monthly or even weekly routine to compact the database.
Database splitting is a must for multi-user environments. Each user should have their own copy of the frontend, linked to a shared backend on the network. If you need more than two gigabytes in your backend, split that backend into multiple files.
Some people worry about the number of database objects. Microsoft says you can have up to 32,000 objects in a database. An object could be a table, query, form, report, macro, or module. In practice, I have never seen anyone get even close to this - most large applications end up with a few hundred objects at most.
For modules (which store your VBA code), the published limit is 1,000 modules. That is extremely generous, and in all likelihood, you will never reach it. When naming objects, Access allows up to 64 characters, but it is best to keep names meaningful but not excessively long. For instance, include key table names in your query objects so you can quickly find all queries related to customers or orders just by typing the table name in the navigation pane.
Password and username lengths listed in older specs are mostly historical since user-level security is not part of modern Access.
Another spec you may see is the 255 concurrent users limit. In reality, you should start considering SQL Server if you have 20 to 30 users actively working in the database at the same time. I have set up Access databases for organizations with up to 50 intermittent users, but heavy real-time use by a lot of people can increase the chances of data corruption and slowdowns. A small office or team is ideal for Access. For best performance and safety, run your backend on a high-quality wired network. Wireless connections are possible, but if the signal drops, you risk corrupting your database.
Other specs, like the maximum number of fields in a table (255), only matter if your table design is poor. If you are getting close to 255 fields, you should probably review your normalization. For example, if you have something like item1, item2, item200 as field names, those should be rows in a new related table, not columns in one table. Same with dozens of phone number or address fields - structure your data for flexibility and future growth.
The maximum per-table data size is also two gigabytes since it is limited by the file size. If you approach this with a single table, consider archiving older data. For example, split your orders table into current orders and archived orders from previous years, and reference both tables as needed.
For text fields, short text is limited to 255 characters, and long text (formerly memo fields) allows up to 65,000 characters for user input, or up to 1 gigabyte when adding data with the API or VBA. If you edit more than 65,000 characters in a form, Access will truncate your data, so be careful if your fields approach that size.
Tables can have up to 32 indexes. Indexes are very useful because they make searching and sorting faster, but there is a tradeoff: every index makes data entry and updates a little slower because Access has to update the index as well. Do not over-index your tables - create indexes only on the fields you actually search or sort frequently.
An important and often misunderstood limit is the 4,000 character per-record maximum when using Unicode compression. Unicode is the standard that supports text in every language worldwide, but it can take up more storage for languages with complex characters. Access uses Unicode compression to store ordinary English text more efficiently. This 4,000 character limit only applies to the combined data in all short text fields in a single record. If you have fields that often exceed this, you probably need to redesign the table and move long text to memo fields, which are handled differently.
For queries, you can reference up to 32 tables in a single query. If your query is so complex that it needs this many, it is likely a sign that you should break up your process into multiple, smaller queries - even 10 tables in a query can get messy and difficult to manage. You can nest up to 50 queries inside each other, but this kind of deep nesting is rarely, if ever, necessary.
Regarding relationships between tables, Access enforces relationships using indexes. There is not usually a hard number for how many you can have, but if you have hundreds of enforced relationships, it is a clue that your application is quite large - yet still probably within Access capabilities, unless you start noticing performance problems.
With sorting and querying, there are limits like 255 characters for the total fields you sort by, 99 AND operators in a WHERE or HAVING clause, and 64,000 characters in a single SQL statement. Most users will never hit these, but very complex dynamic SQL built with VBA can occasionally run into the SQL statement size limit.
For forms and reports, Access allows up to 22 inches in width, although Microsoft is working to increase this for modern widescreens. You can nest up to seven subforms or subreports, and you can have up to ten groupings in a report. If you find yourself needing more than this, you probably want to reconsider report design to keep things manageable for the end user.
A quirk in Access is that each form or report only allows about 754 controls or sections added over its lifetime. This does not mean you can have 754 controls on a form at one time, but if you create and delete them repeatedly over time, Access keeps track and eventually you can hit this ceiling (very rare except for very old, heavily redesigned objects).
Another oddball is the report page limit: you can print up to about 65,000 pages in one go. If your reports approach this size, paper costs are likely a bigger concern than database limits.
Macros can include thousands of actions, but if your macro is getting that big, it's probably time to switch to VBA, which is more flexible and easier to maintain for complex logic.
It is important to note that while all these limits are documented by Microsoft, recent tests by experts in the community, like Colin Riddington, have shown that some of these numbers are not always set in stone. In some cases, Access will let you exceed the published limits under certain conditions, likely because the product has been updated over the years and some documentation is out of date. Still, these specifications are great guidelines, and most users will never need to worry about surpassing them.
In reality, most problems with Access databases come from design issues, not from hitting these technical limits. If you are nearing one of these numbers, it is usually because the database structure needs a review - perhaps some normalization, or separating current and archived data, or splitting large backends.
To summarize, Access can easily handle most small to medium business needs if you design your tables well, avoid storing large objects inside the database, compact regularly, split your frontend and backend, and limit heavy use to a couple dozen simultaneous users on a reliable wired network. If you find yourself outgrowing Access or hitting hard limits, that is a sign you are ready for the next step, such as migrating your backend to SQL Server.
If you have ever run into an Access limit or have an unusually large Access database, share your experience. There is always more to learn from the community, and sometimes the best solutions come from someone who has faced the same challenges. With good design practices, you will find Access can support most business data needs for many years to come. |
