How many records will Access store?

[Bayou BoB]

Hello;

Simple question for most I'm sure. I'm curious how many records MS
Access will store in a single database? What happens when you reach
that number of records? Do you just lose the ability to enter new
data? Or will it crash on you? What does one typically do when you are
approaching the end of Access' ability to store data? Additionally,
how many records will SQL store? Thanks!

Kevin

[Douglas J. Steele]

There isn't a maximum number of records, per se. What there is is a maximum
file size for an MDB. For Access 97 and earlier, it's 1 Gb. For Access 2000
and newer, it's 2 Gb.

Exceeding that size can cause Access to fail.

AFAIK, the same is true for SQL Server. There's no maximum number of records
per se. The number of rows is limited by available storage (and the maximum
database size is 1,048,516 TB)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Bayou BoB" <ca*******@hotmail.com> wrote in message
news:qc********************************@4ax.com...

Hello;

Simple question for most I'm sure. I'm curious how many records MS
Access will store in a single database? What happens when you reach
that number of records? Do you just lose the ability to enter new
data? Or will it crash on you? What does one typically do when you are
approaching the end of Access' ability to store data? Additionally,
how many records will SQL store? Thanks!

Kevin

[Bayou BoB]

Thank you kindly for the clarification. I appreciate it!

Kevin

[NB]

This may be added: since you can link tables from other mdb files, the
2GB limit is, therefore, not the limit of an Access database.

However, saying so does not mean that Access database size can be
unlimited. I think Access may have some performance problem when the
number of records in a single table reach approximately 400,000 -
600,000 (depending on the average size of a record). Although I have
heard of Access databse with table having ~ 1 million records.

Personally I think when a database reaches that size, one should
seriously consider migrating it to SQL Server or Oracle

NB

[Tony Toews]

ni******@lycos.com (NB) wrote:

However, saying so does not mean that Access database size can be
unlimited. I think Access may have some performance problem when the
number of records in a single table reach approximately 400,000 -
600,000 (depending on the average size of a record). Although I have
heard of Access databse with table having ~ 1 million records.

A friend has 100,000,000 records consisting of the daily closing stock prices. And
he can get to any stocks records in less than a second. Obviously he has indexed the
appropriate fields. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm