LImitations of Access

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?
suhaimeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nico5038Commented:
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.

Nic;o)
0
aflockhartCommented:
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.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

neltanCommented:
There is a connection limit, right?
Access allowed up to 10 connection from VB or ASP when I used Access 97.
0
monosodiumgCommented:
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.
0
Steve BinkCommented:
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.

routinet
EE Cleanup Volunteer
0
Luke ChungPresidentCommented:
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:
http://www.fmsinc.com/microsoftaccess/databasesplitter/index.html0 
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
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html
b. Performance Tip: Set table Subdatasheet Name Property to [None], or explicitly specify the name
http://www.fmsinc.com/free/newtips/access/subdatasheetname.asp
c. Microsoft Access Performance Tips to Speed up Your Access Databases
http://www.fmsinc.com/microsoftaccess/performance.html
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:
http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html 
Good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.