Maximum number of records

[Mike]

How many records can be stored in one table? Is there any
maximum limit?

 

[Jeff Boyce]

Mike

Depending on version, Access holds 1 (or 2) Gbytes. While there is some
overhead for the internal structures, you could theoretically have close to
that as the sum of all tables' size within one .mdb file. If you had tables
that big, you'd almost certainly want to index them for performance --
indexes take up more room, too.

Each record has a (I believe) 2048 byte size limit.

Are you asking out of prurient interest, or is there a practical issue? Are
you trying to decide if you should use a more robust back-end data source?

Good luck

Jeff Boyce
<Access MVP>

 

[Rover]

Where can I verify that max record size? I have an ODBC table coming to
me that has one field that is 2380. Will it work in Access?

 

[Rover]

A2K 9.0.2720

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

 

[Jeff Boyce]

As I understand it, that will be too large to fit into anything but a memo
field. Do you have a way to link to the table, rather than importing it?

Jeff Boyce
<Access MVP>

 

[Stargazers]

Hi Jeff,
I also wanted to know what's the max number of rows a table can have. I dun really understand your answer above but i hired a programmer (still a student) who told me that a table can only have abt 2000 lines of records per table cos I'm running the Database in a PC, and not a server. I have abt 30 tables currently.

Can you advise if that's logical or even remotely true? I just find that hard to believe since Excel can have abt 65k records in a worksheet. I do know that a server offers better storage capability but that's not an option for me right now.

Thanks in advance for any help given.

~Stargazers

 

[Tazz092]

I don’t think that there is a limit of the number of records you can have in a table, But there is certainly limit on file size. The Access can not get bigger then 2g. I know people who have up four million records in one table. How this answer your question.


Dr T