Maximum number of rows in an MS Access database engine table?

Asked 2009-08-03T09:35:04.883

We know the MS Access database engine is 'throttled' to allow a maximum file size of 2GB (or perhaps internally wired to be limited to fewer than some power of 2 of 4KB data pages). But what does this mean in practical terms?

To help me measure this, can you tell me the maximum number of rows that can be inserted into a MS Access database engine table?

To satisfy the definition of a table, all rows must be unique, therefore a unique constraint (e.g. PRIMARY KEY, UNIQUE, CHECK, Data Macro, etc) is a requirement.

EDIT: I realize there is a theoretical limit but what I am interested in is the practical (and not necessarily practicable), real life limit.

ms-access jet ms-jet-ace

Edit Report

2 Answers

Here's my attempt:

I created a single-column (INTEGER) table with no key:

CREATE TABLE a (a INTEGER NOT NULL);

Inserted integers in sequence starting at 1.

I stopped it (arbitrarily after many hours) when it had inserted 65,632,875 rows. The file size was 1,029,772 KB.

I compacted the file which reduced it very slightly to 1,029,704 KB.

I added a PK:

ALTER TABLE a ADD CONSTRAINT p PRIMARY KEY (a);

which increased the file size to 1,467,708 KB.

This suggests the maximum is somewhere around the 80 million mark.

answered 2009-08-06T09:43:51.527

It all depends. Theoretically using a single column with 4 byte data type. You could store 300 000 rows. But there is probably alot of overhead in the database even before you do anything. I read some where that you could have 1.000.000 rows but again, it all depends..

You can also link databases together. Limiting yourself to only disk space.

answered 2009-08-03T09:57:22.537

Maximum number of rows in an MS Access database engine table?

2 Answers

Your Answer