how much data can be stored in access database 2

[earlrainer]

Hi,

I want to know what is the maximum number of rows that can be put into access.

and is it true that access cant handle more then 10 users at the same time

 

[Biffy13]

Hi

I don't know this for fact but I think it can only handle up to 35 users at a time. If you are thinking of using it on a website, consider using something like MySQL instead which is far more robust.

As for size limit...I think you can only go up to about 1Gb - I guess the number of rows depends on the size of the data.

For what purpose are you wanting to use Access?

 

[psemianonymous]

Access, being a file-based database, can only handle a certain number of users. This magical number is determined by the capabilities of your file server, the quantity of data being pulled by any given user, and the size of the existing database.

Basically what I'm saying is that your application may be able to hold 35 users concurrently, but it may not even be able to handle 5.

Design is important here. If you absolutely can't use something other than access, you can use a bevy of tricks to reduce network traffic (and thus traffic to your backend MDB file) and allow more users to concurrently use the database.

So to definitively answer your question: maybe.



As for Access size limits, it is 1GB for 97 and 2GB for 2000/2002. The size is only bloated quickly if you use OLE-embedded images, so this means you will be fine doing data entry for the next 10 years into one of these databases.


So what, you may ask, happens when the Access database is overused? Recurring database corruption--that and noticeable delayed responses.

 

[grnzbra]

NOTE: By using linked tables, the size limits 1Gig or 2Gig, can be beaten. Put your database, without its tables, in one database. Then put each table in its own database and link to the tables.

If you have a single table that is or is going to get too large for 1 database, (ie. closing stock prices for each stock on the NYSE and the AMEX etc, for each day of trading) you could split the table along logical lines such as NYSE prices for a given year and AMEX prices for a given year. Then, if you need to use all the info, create a UNION query to bring it all together.

As far as beating some max users limitation, you could try using unbound forms in which you open a connection to the back end tables database and a recordset of the info you need, and then close the connection. The values in the recordset are moved to individual fields on the form in your database. When you are done making changes, you open another connection, insert ay changes back into the table and close the connection. This technique was discussed in the January 2002 issue of Inside Microsoft Access.

 

[Stevehewitt]

If you can get your hands on SQL Server of something along those lines with a standard server then that will sort you out for years to come. Simply create a MDE file for each user.

No probs!

Steve Hewitt

 

[earlrainer]

Thank you guys for your inputs

 

[Wildcat98]

This is the information that is given in the Access 2000 help when I searched for ACCESS LIMITATIONS.
------------------------------------------------------------------------------------------
Microsoft Access database general specifications

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 (Note from Wildcat98: This would be a very slow database... Especially if you are working with a large amount of data.)
-------------------------------------------------------------------------------------------------

Microsoft Access database table specifications

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


Like I said all I typed was ACCESS Limitaions and you will be able to find all of the Access Specs within the Help file that come with Access.

Hope this helps.

Kale (Wildcat98)