By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,498 Members | 835 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,498 IT Pros & Developers. It's quick & easy.

How many records will Access store?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
Thank you kindly for the clarification. I appreciate it!

Kevin
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.