What is the size limitation of an Access database?

[Guest_imported]

I am fairly new to this, but I need to know what the limits are on both tables and database size for Microsoft Access 2002. I have read that the tables are limited to one megabyte, but no definite answer on the overall size of the database. Can anyone offer assistance?

 

[ToeShot]

I know in A2K the max DB size is 2 gig So I would expect 2002 to do at least that much. but if like to do a lot of coding you will cut your size down. should not be consideriable though. as for table Size I am not sure. Sorry

 

[Ed2020]

Depends on what version of Access you are using.

Access 2000 - maximum size of a table/mdb file is 2GB.
Access 97 - maximum size of a table/mdb file is 1GB.

No table should ever reach 2GB though - if it does then it probably needs splitting into separate tab;es. If your mdb reaches the limit simply split the tables across multiple mdbs and link them to a common front-end.

I reality the maximum size of an MS Access system is virtually limitless.

Incidentally the maximum limit is slightly lower than those quoted above because Access generates temporary tables when running queries.........

HTH,

Ed Metcalfe
ed_metcalfe@hotmail.com

 

[Guest_imported]

Is there a recommendation on the number of concurrent users for Access 2002?

 

[ags91]

hi all,

from what i can see at the moment it is not much different to a2k. limitations are 2 gig file size, 255 concurrent users, all the same as a2k.

Andrew

 

[nashman]

Although the documentation does state 255 concurrent users, in the real world, the maximum number of users is greatly determined by the sturdiness of your network backbone, speed and bandwidth capabilities. On most systems that I have worked on, more than 25 concurrent users and you will probably run into some problems. The problem with Access is that it can only handle a single query request at a time. So if you have multiple users at once requesting query runs, they will be executed in priority sequence ( in the order in which they were received ). Unlike Oracle which can run multiple concurrent queries.

Nash

 

[Ed2020]

I agree that the limitations on concurrent users are largely down to your network bandwidth but there are certain ways to improve the situation.

Avoid using bound forms and also, wherever possible, perform data operations in SQL rather than using recordsets.

You also have to bear in mind that the theoretical 255 users relates to the front end. If you set up and front-end/back-end system then not all the users will necessarily be accessing the backend at the same time, and they can all have their own frontend.

You could use temporary tables to store data in the frontend and then write the data to the backend in a single operation when required, saving network traffic and backend data store use.

HTH,

Ed Metcalfe
ed_metcalfe@hotmail.com