Max Number of Records in a Table Challenge

[Guest]

The question of how many records can an Access table hold is often asked so I
decided to experiment.

133,674,315 records.

That's 133 million records plus change that I squeezed into a table within
an Access 2003 MDB database file. It's possible to show 133,674,316 records
(1 more) however when closing the table you get an error.

While doing some experiments with other table formats, like Access 97, I was
also able to get 133 million records a few different ways but 133,674,315
records was my best result.

Anyone think that they can beat that number?

 

[Douglas J. Steele]

What were the characteristics of the table, Jerry? How many fields? What
field types? etc.

 

[Jamie Collins]

On a more philosophical note, what's the structure of the table, and is a
table without a primary key really a table?

No, a table must have a key.

What is an 'Access' database? I created a new mdb using
ADOX.Catalog.Create and with the ActiveConnection I created a table:

CREATE TABLE x (x INTEGER PRIMARY KEY)

and loaded it with unique values. After 94,198,689 rows I get, 'Invalid
arguement.' A lot less than I was expecting!

Jamie.

--

 

[Roger Carlson]

Well, Don beat me by a mere1020 records. Best I could do was 133,677,120.
Linked the table into a front-end and used DAO to fill a Yes/No field with
True. Used Access 2003 in 2000 format. I can still open the table from the
front-end to get the recordcount, but I can't open the database anymore.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jamie Collins]

My point precisely.

Agreed. I think others in this thread are missing the point.

if you compact the database after the "Invalid Argument" messsage,
does its size diminish significantly and can you then add more records?

Good point. I compacted the mdb using JRO.

Note the cardinality was actually 9,331,4700: the value I quoted
earlier was erroneously returned pre-compact by the adSchemaStatistics
rowset (OpenSchema method), fine post-compact.

The file size reduced from 2097MB to 1464MB.

A new single-row table (which I promptly dropped) had appeared:

INSERT INTO MSysCompactError
(ErrorCode, ErrorDescription, ErrorRecid, ErrorTable)
VALUES (-1812, 'Could not find field ''Description''.', NULL, 'x')

The table seems to be pretty useless e.g. SELECT MAX and SELECT COUNT
took several minutes each (what, Jet doesn't use its own statistics for
these?!)

I could indeed add further values to the table but I noticed - too late
- the PRIMARY KEY had been lost during compact.

I went back to the original mdb, did the JRO compact again and tried to
rebuild the PK:

ALTER TABLE x ADD PRIMARY KEY (x)

but after 220 seconds it errored with, "Not enough space on temporary
disk"

In conclusion, a table without a PK is not a table and if comapcting
removed the PK then I ain't got a table. I think the real challenge
(i.e. a table with a PK *after* compact) has yet to be met.

Jamie.

--

 

[Roger Carlson]

While I still think it's a valid question to find out the very maximum
number of records in an Access table (primary key or not), it is also
equally valid to ask what the absolute maximum number of records there can
be in a table with a primary key (and therefore at least one index).

To find that, I created a table with an autonumber primary key. As a simple
list of numbers, it's still pretty useless, but at least it satisfies the
definition of a table in a relational database.

I used a DAO recordset on a linked table in the Front-End database to
populate the table. When I got my first Invalid Argument error, the file
was 2,097,152KB and had 93,774,465 records. However, I was able to compact
the database down to 1,471,200KB and then add more records. To my surprise,
I came up with 133,676,610 records, almost as many as when I used a Yes/No
field with no index. I was once again able to compact the database to
2,097,132 KB and run the process again. When it errored, however, it had
not added any new records. So I compacted again, ran the process again, and
this time, I came up with 133,677,120 recorrds, exactly what I got with the
Yes/No field and no index. After that, while I could open the database
programmatically to count the records, I could not open it in the Access
GUI.

Interesting. Looks like 133 million and change is the largest "real" table
as well.