Limit of Access

[reneford]

Hi,

I would like to know what is the limit of record an access bd can contain?

Also, when is the performance can degrade because there is too record?

Thanks and sorry for my english... ;o)

 

[rjoubert]

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


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

 

[missinglinq]

Go to Help in whatever version of Access you have and enter

Specifications

Click on Microsoft Acccess Specifications

and it'll give you all the specs.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[WildHare]

..and to answer your second question, "It Depends..."

Several things can affect the 'speed' with which your database runs. However, here are some general rules:

1) Memory and CPU speed are always very good. The more, the merrier. I have run databases with over a millon records on a single PC, but have made sure that we had plenty of RAM. CPU speed is not as critical, nowadays in the world of multi-gigahertz Pentiums and AMD processors. I'd rather have a GB of RAM over a GB of CPU speed...

2) Make good use of indexed tables when you can, but don't over-index. Each record revision may require multiple indexes to be rebuilt, which adds to overhead.

3) Watch those joins when they aren't really necessary.

4) Keep record lengths as short as possible. Several small tables are usually better than one large table, so normalization will help, to a certain degree.

If you have a Front End/Back End situation, and the data tables are kept on a network volume, remember that ALL data sorting and retrieval will be done on the local PC - the network will have to send all the data to the local pc for any queries, etc. This is the biggest bugaboo regarding splitting a database. Access is not a database engine like SQL that can run a query remotely and then pass only the needed data to the local PC.

So in some cases, the speed issue will be network-dependent more than anything else. Much of the "wait time" will be I/O between your local PC and the network drive. The only way to get around this is to be careful how you structure your application, to reduce the number of times large tables have to be sent down the pipe. Also, you can save a bit of time, especially with a shared, networked database, by making sure the tables are compacted on a regular basis.





"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[pvsmith5]

I've put as many as 6.5 million records that had more than 25 fields into an Access 2003 database. There was no degradation in performance and it never crashed.