Capacity of Ms-access

[sureshbaba]

hi,

i am desining a database that have upto 20 tables, many tables have more than 200 fileds.

and there will be a huge amount of data that has to be stores there.

i am using MS_Access 2000.

1. l there aby problem with access becouse of huge amount of data?

2. Maximum how many rows can be stored in ms access?

3. what are the steps i can take for optimizing perofrmance?

hope i will get suggesion here

bye

[aio]

Many tables with over 200 fields? Check that out. Seems like design error to me.

Frankly, I don't know the limits. I have MS Access with 1.4M records (so far, my highest) and it's still processing well -- Though in the past, I have read that 400,000 is more than enough. But many of those I read suggests that instead of putting all tables in a single database, just create a series of linked tables contained in several dbs.

[aio]

One discussion can be found here (http://www.codeguru.com/forum/showthread.php?s=&threadid=191888).

[sureshbaba]

Thank you aio for your reply,

but there are 200 fileds, its no design error!

In my project there are differest checklists with that number of checks. i think i have no other way.

the link u given also useful

bye

[antares686]

Actually it is not uncommon to see this kind of design but I don't recall a width limitation. I do believe there is a 2GB limitationm for the database and other potential issues using Access across a network can cause. However with a large checklist it is more common to see done simialr to this.

--------------------------------
Table CL_List

PK_CLID Number (Autonumber)
CL_Name Text 100

--------------------------------
Table CL_Items

PK_ItemNumber Number (Autonumber)
FK_CLID Number (Foreign Key to PK_CLID)
ItemTitle Memo or Text(255)

--------------------------------
Table CL_Entries

CL_Number Number (Requires build your own Unique ID for checklist to be able to reference all related items)
FK_ItemNumber Number (Foreign Key to PK_ItemNumber)
Answer (depends on type of datatype needs).

Composite key on CL_Number & FK_ItemNumber

This basic layout means tables are narrow and more managable than very wide tables.

[Jasper XAFAX]

Hi,

Iv'e red somewhere the recordlimmitation is 99.999.999 records. But I could be wrong.

Jasper

[Thread1]

I thought the limitation of an access table does not reflect on the number of records. But the filesize..

1.) Older Version : 1.1G - 1.3G bytes..
2.) Current Version : Only doubles the previous version (Unicode format)

[sureshbaba]

hi,

I read the docementation with MS Access 2000.

The following things i like share with you.

1. The size of the database is 2GB. But if u want moe u can achive it with linked tabels. So the only limitation is the memory capacity available.

2. The table size is also upto the size of the table not the number of records. The maximum size is 1 GB.

bye

Suresh

[Andrew R.]

There is an article Microsoft Access 2000: Choosing between MSDE and Jet (http://www.microsoft.com/sql/techinfo/deployment/70/msdejet.asp) which lists all the limitations of Access database in the Word file (http://www.microsoft.com/sql/techinfo/deployment/70/Access2000MSDE.doc).
Here is extract:
"Appendix B: Access/Jet (.mdb) Specifications

File size (.mdb filetype) 2 GB
No. of objects in a database 32,768
Modules 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 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.
Table size 1 gigabyte
Number of characters in a Text field 255
No. of characters in a Memo field (data entered via UI) 65,535
No.of char. in a Memo field (data entered programmatically) 1 gigabyte
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 2,000
Number of characters in a field property setting 255"