LImitations of Access

suhaime used Ask the Experts™
I would like to know the limitations of access in terms of

1. how many rows it can process
2. how many column it can process
3. what is the best solution if your database exceeds the limitation other that converting to ms sql or any other high end database software?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project manager
The following are Access table limitations:

Microsoft Access database table specifications
Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255

The following are general Access limitations:

Microsoft Access database general specifications
Attribute Maximum
Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255
1) No row limit, total .mdb size is limited by 2Gb
2) Max 255 columns
3) Use ODBC and e.g. MySQL

In general the 2Gb limit will be large enough, but when needed you can place tables in different .mdb's and link them to a "master" .mdb, making the effective size many times bigger.

The maximum numbers that Access will support are in the previous replies.You will probably find other limitations before you hit these, such as the speed of your processor and the amount of memory that you have. Things will slow down as your database gets more data to deal with. There is no pre-set limit to number of rows in a table, but the more you have, the slower everything will become.

If you need more columns than 255 , you can join tables together based on the same key field; but usually you are best to reconsider your data structure before you get to that stage.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

There is a connection limit, right?
Access allowed up to 10 connection from VB or ASP when I used Access 97.
Way before you hit the limits, you are likely to find Access unusable in a multi-user networked environment because all the query processing takes place locally, which means large columes of the mdb file have to be sent to client machines where the query is then executed. This usually ceases to be practical well before you reach the 2G limit and then you would want to move to a proper server-based RDBMS like SQL svr.
Top Expert 2004

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: Dhaest {http:#11303998} & nico5038 {http:#11304011} & aflockhart {http:#11304247} & monosodiumg {http:#11304904}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer
I think the multi-user limitations are overstated.  Access can handle a very significant number of users depending on what's being done.
Most Access databases have a fairly small amount of data (usually less than 50 MB).  While this may have been a problem when Access debuted in 1992, it's hardly a problem today. On a standard network, that amount of data can go over the network and reside in RAM very quickly. In reality, selections of a table or query require a subset of the database and even less if indexes are used.
Viewing data and running queries in that environment is very fast and is often much faster than a SQL Server environment.  Of course SQL Server offers a lot of other features, but performance is not one of them for small databases.
Criteria to consider for Access in multiuser environments are:
1. Properly designed Access database architecture with a front-end/back-end split database design.  This is practically mandatory for multi-user Access applications for maintainability and performance reasons.
More info in this paper Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability: 
2. Making sure tjhe application, queries, forms, reports, etc. are optimized. If it's crappy for a single user, it'll just get worse with more users. Of course, this is true whether you're using Access, SQL Server, Oracle, Java, etc. Here are some papers:
a. Significantly Improve the Performance of Microsoft Access Databases with Linked Tables
b. Performance Tip: Set table Subdatasheet Name Property to [None], or explicitly specify the name
c. Microsoft Access Performance Tips to Speed up Your Access Databases
2. Number of simultaneous users (the total number of users doesn't matter). We have applications supporting hundreds of users but they aren't all on it at one time.
3. What the users are doing.
If they are simply doing data entry, you can support a huge number of people because people just can't type that fast and traffic only occcurs when records are posted.
If the users are doing searches (readonly) on large tables, that will have performance issues. If they are runnings lots of action queries, that'll also degrade performance. If its reporting or batch processing, performance may not be so critical
4. Scalability. If the database gets too large and I/O performance degrades, migrating to SQL Server should be considered. Access lets you keep the front end application and link to the back end database.
Visit our Microsoft Access to SQL Server Upsizing Resource Center for more info: 
Good luck!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial