What is the max number of...

[Mike C]

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.

 

[Dirk Goldgar]

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.

 

[Mike C]

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

 

[Dirk Goldgar]

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.

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.

 

[Mike C]

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.