What is the max number of...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 18 Jan 2008 15:09 GMT

What is the max number of records that can be put into a table in Microsoft
Access? Does it vary from version to version? Thanks in advance.

Reply to this Message

Dirk Goldgar - 18 Jan 2008 15:21 GMT

> What is the max number of records that can be put into a table in
> Microsoft
> Access? Does it vary from version to version? Thanks in advance.

Google or the online help would be your friend on this one. There's no
fixed limit, there's only the limit on the overall size of the database
file. In Access 2000 or later, the .mdb or .accdb file can't exceed 2GB,
while Access 97 has a limit of 1GB. That's room for a lot of records. If
you're getting close to that size in a _compacted_ database, it's time to
start looking for a different back-end data store, such as SQL Server.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Mike C - 18 Jan 2008 16:01 GMT

Dirk,
Thanks for responding so quickly. I always thought 2 million records was
the limit and maybe I mistook that for the 2 gig limit. But I'm taking over
someone else's job and the database they have been using is only 89 meg but
there is a table in it with 2.7 million records. It's freaking me out. Data
will continue to be dumped in there for the next 5 years or so and I'm not
comfortable with 2.7 million records in a table in case there is some kind of
limit. I'll see what I can google though.
Mike

> > What is the max number of records that can be put into a table in
> > Microsoft
[quoted text clipped - 6 lines]
> you're getting close to that size in a _compacted_ database, it's time to
> start looking for a different back-end data store, such as SQL Server.

Reply to this Message

Dirk Goldgar - 18 Jan 2008 17:02 GMT

> Thanks for responding so quickly. I always thought 2 million records was
> the limit and maybe I mistook that for the 2 gig limit. But I'm taking
[quoted text clipped - 7 lines]
> of
> limit.

Those must be very small records! Assuming that this table actually resides
in the database, and is not linked from elsewhere, that's on the order of 32
bytes per record. Are you sure your information is correct?

The number of records itself shouldn't be a problem, so long as they're
suitably indexed for whatever you want to do with them. Without the proper
indexes, most operations on that many records will be slow. But indexes do
make a database bigger, so there's a trade-off that you must consider.

Still, I have a working database with 18.5 million records in its largest
table, all properly indexed. This is a *very* big .mdb file -- about 1.6
GB -- but it's perfectly functional.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Mike C - 18 Jan 2008 19:00 GMT

Well I'm very happy to hear that you've got a table with over 18 million
records in to regardless of the file size.

2.7 million records and only 89 megs... we're talking 16 fields in the
table, 10 text fields nicely trimmed (most of them 3 to 5 chars) and 6 number
fields that are all decimal. And you were right, none of the fields are
indexed so I guess that would also make a difference.

I learned a lot today, thanks again.

> > Thanks for responding so quickly. I always thought 2 million records was
> > the limit and maybe I mistook that for the 2 gig limit. But I'm taking
[quoted text clipped - 20 lines]
> table, all properly indexed. This is a *very* big .mdb file -- about 1.6
> GB -- but it's perfectly functional.

Reply to this Message