Maximum Number of Records?

What is the limit on the number of records in an access database?
LVL 5
jjacksnAsked:
Who is Participating?
 
GRayLCommented:
Here's what I get from Help, Answer Wizard - Specifications.

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

See Table size
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
There is no record limit in an Access table, only a file size limit of 1MB up to Access '97, and 2MB for all other versions.

Having said that, Access does slow down considerably after 1M records, and most developers I know would consider another back-end such as MySQL or SQL Server if they hit that mark.  The 1M figure will vary depending on developer preference, business availability, number of concurrent users, and other things.
0
 
hongjunCommented:
I thought it is 2GB.
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Oops, GB instead of MB.
0
 
sparabCommented:
Here is the link Documented

http://www.databasedev.co.uk/access_specifications.html#General

There is no records limit. but When it is about to reach size of 2 GB then it database performace comes down.

thanks
sparab

0
 
jerryb30Commented:
Are you trying to calculate max number of records for a table within a database?  
Add max size of each field in bytes for max recordsize in bytes.
Divide into 1 billion or 2 billion, and you will get a rough estimate.
0
 
GRayLCommented:
BTW my excerpt is from A2K Help
0
 
GRayLCommented:
Thanks, glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.