About Access limits

[josef19]

Hi:

OK, I am very confused. I need to know HOW MANY RECORDS CAN BE STORED IN A
SINGLE TABLE in MS Access.

I found that a table can be as big as 2Gb (Access 2003). If each record
in
my table has a maximum size of 1177 bytes, that means I can create a
maximum
of 2Gb/1177=1,824,540 records minus the size of system objects. However,
I
know that the maximum amount of objects in a database is 32,768. So is
this
the maximum limit for the number of records or is 1,824,540?

Thanks!

 

[Manuel S.]

Hi:

OK, I am very confused. I need to know HOW MANY RECORDS CAN BE STORED IN A
SINGLE TABLE in MS Access.

I found that a table can be as big as 2Gb (Access 2003). If each record in
my table has a maximum size of 1177 bytes, that means I can create a maximum
of 2Gb/1177=1,824,540 records minus the size of system objects. However, I
know that the maximum amount of objects in a database is 32,768. So is this
the maximum limit for the number of records or is 1,824,540?

Thanks!

 

[Allen Browne]

The 2 GB limit is the only practical one.
There is no 1.8 million limit.

If a table has an AutoNumber, its theoretical limit is around 2 billion.

 

[Jeanette Cunningham]

Hi Chris,
interesting facts. I am interested to know how you calculated them - what
assumptions did you make and what other known figures did you use - if any.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[George Hepworth]

.... the maximum would be around 1.57 million. That's an interesting theory.

However, I just imported a SQL Server table of 2,714,925 records into a
single table in an Access mdb. This little test mdb has just the one table,
which I imported to verify your assertion that the maximum would be around
1.57 million.

Two test queries, one a simple select, the other an aggregate query, each
run in under 10 seconds, for whatever that is worth. Of course there are
only the three objects in the mdb at the moment, too.

The total file size of this test mdb was 452,668 KB, which is pretty large
in itself. I then indexed two of the fields in the table. The file size,
with the indexes, grew to 475,408 KB. Still that's theoretically not quite
1/4th of the capacity of an mdb.

 

[Dirk Goldgar]

... the maximum would be around 1.57 million. That's an interesting
theory.

However, I just imported a SQL Server table of 2,714,925 records into a
single table in an Access mdb. This little test mdb has just the one
table, which I imported to verify your assertion that the maximum would be
around 1.57 million.

Two test queries, one a simple select, the other an aggregate query, each
run in under 10 seconds, for whatever that is worth. Of course there are
only the three objects in the mdb at the moment, too.

The total file size of this test mdb was 452,668 KB, which is pretty large
in itself. I then indexed two of the fields in the table. The file size,
with the indexes, grew to 475,408 KB. Still that's theoretically not quite
1/4th of the capacity of an mdb.

It seems to me that Chris's figures were based on specific maximum record
size that the OP said he's dealing with: 1177 bytes. If that figure is
correct, and *if* all records are in fact that big (minimum = maximum), then
the rest of the calculation flows naturally from Jet 4's page size (4K) and
the stipulation that a Jet 4 .mdb file can contain a maximum of 524,288 data
pages. I don't know if that last figure is correct, but it sounds
plausible.

In the case of your counterexample, George, I think you're ignoring the
record-size factor.

 

[Tom van Stiphout]

On Thu, 22 Jan 2009 17:57:01 -0800, Manuel S.

A record in a table does not count as an object. The entire table
counts as one object.
If you have Text columns in your table and don't fill them to the max,
your record size will be less than that max. Those columns work
similar to SQL Server's varchar column.
If you use Unicode each text character is two bytes.

-Tom.
Microsoft Access MVP

 

[George Hepworth]

For the heck of it I copied that 2.7 million record table in my little test
mdb. Then I decided to give it a real work out and append the contents of
the second table to the first, for a total of 5,429,850 records.

So now my not-so-little test mdb has two tables, one with over 2.7 million
records, and the other with over 5.4 million for a total of more than 8.1
million records in two tables, weighing in at just over 1.4 GB total file
size.

It's obviously unwieldy at that size and would never work as a real back
end, but the point, I believe, is that Access can indeed hold a surprisingly
big chunk of records.

 

[George Hepworth]

Of course I am ignoring the record size factor, but the point is that 1.57
is NOT the maximum. The actual maximum is that it DEPENDS.....

 

[John W. Vinson]

Your test is using smaller records than Manuel's is. Manuel says his records
are 1,177 bytes each. Make all your records 1,177 bytes each and remove the
indexes. See how many records you can fit in a Jet 4 db table. It won't
come close to 2.7 million, even when it's stuffed to the gills.

Well, actually he said "each record in my table has a *maximum size* of 1177
bytes" - most are presumably smaller, though he didn't say.

I agree that this database is a very good candidate for moving the data to a
client/server backend such as SQL.

 

[Dirk Goldgar]

Of course I am ignoring the record size factor, but the point is that 1.57
is NOT the maximum. The actual maximum is that it DEPENDS.....

No doubt about it. For Manuel, the important questions are (a) whether his
maximum record size calculations are correct (often they are not, in my
experience), and (b) what is the *average* record size.

 

[Manuel S.]

Hi, George:

.... the maximum would be around 1.57 million. That's an interesting theory.

However, I just imported a SQL Server table of 2,714,925 records into a
single table in an Access mdb. This little test mdb has just the one table,
which I imported to verify your assertion that the maximum would be around
1.57 million.

So you mean I can insert more than 1.57 million records? I need to
determine how many can be inserted!

 

[Manuel S.]

I was trying to correct Manuel's calculation "that means I can create a
maximum of 2Gb/1177=1,824,540 records minus the size of system objects".
Because of the way Jet stores data in data pages, I wanted to point out his
limit is lower than that while using the same record size he did in his
calculation. Comparing apples with apples. I seem to have started an
argument tonight.

I'd say you're right about moving the data to something more scalable if
Manuel's concerned about how many records will fit in a 2GB db file.

Chris

Hi, Chris:

OK, thanks for all the info and calculations.... yes, probably I will have
to move to another db. The problem is that it should be preferably
"serverless", since it depends on another server, which indeed works with 2
other servers, so I don't want to make it harder. I was checking also sqlite,
I will try it and see if it is a good choice.

Thanks anyway for the advice!

 

[George Hepworth]

My little test was done with a table in which the record sizes are
considerably less than the 1,177 bytes you specified.
If every record in your table is at that maximum size, then the math
calculations Chris posted do indeed apply. Math is math. On the other hand,
with record sizes less than that, your table will hold more records as I
illustrated.

Thus my earlier response also applies: it depends on your actual data. And
that is all I was trying to say.

 

[John Spencer (MVP)]

Well, he did say a record would have a maximum size of 1177. Access records
don't use the maximum size when they are stored. If the record has a text
field that is defined as 255 characters in size and one character is entered
into the field then the size of the record is only increased by 1 and not 255.

I'm not arguing with your calculations. Just reminding you that using the
maximum size gives you a lower limit of the number of records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

OK, I am very confused. I need to know HOW MANY RECORDS CAN BE
STORED IN A SINGLE TABLE in MS Access.

How long is a piece of string?

It may be that you mean "in a Jet database," since Access can be
used to build an application that uses any database engine as the
back end, and the limits of how many records (if there are any)
would be coming not from Access but from the database engine you're
using.

I found that a table can be as big as 2Gb (Access 2003).

And MDB file can be 2GBs in size.

If each record in
my table has a maximum size of 1177 bytes, that means I can create
a maximum of 2Gb/1177=1,824,540 records minus the size of system
objects.

See -- you can figure it out. If your records are half that size,
you'll get roughly twice as many records, and if they are twice that
size, the record limit will be half that.

So, again -- how long is a piece of string?

However, I
know that the maximum amount of objects in a database is
32,768. So is this the maximum limit for the number of records or
is 1,824,540?

If you are smart enough to calculate the record size, then you
should be smart enough to figure out the answer yourself.

In case you're not getting it:

NO THERE IS NO MAXIMUM NUMBER OF RECORDS IN A JET DATABASE.

Well, I guess you could create a table with one field with the
smallest possible data type and then populate that to try to figure
out how many records could fit and that would tell you the maximum
number of records with the smallest possible record.

But since such a table would be completely useless, that wouldn't be
a very useful number, now would it?

How long is your piece of string?

If you're worried about the 2GB limit on file size, then pick a
different data store, such as SQL Server or MySQL.