473,289 Members | 1,963 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How many records will Access store?

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
4 19621
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
Thank you kindly for the clarification. I appreciate it!

Kevin
Nov 12 '05 #3
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
7
by: Eric Slan | last post by:
Hello All: I'm having a problem that's been baffling me for a few days and I seek counsel here. I have an Access 2000 DB from which I want to run several reports. These reports are...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
5
by: Grant | last post by:
Hi Is there a way to recover deleted records from a table. A mass deletion has occurred and Access has been closed since it happened Louis
35
by: deko | last post by:
Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 =...
2
by: PeteCresswell | last post by:
This is pursuant to another thread I tried to start, but can't find on my server. I've got to store rolling rates of return for investment funds - calendar year, quarterly, and monthly To cut...
9
by: thomasp | last post by:
First of all, thanks for the help on my previous VB.NET/MS Access questions. This time I need do the following 1. Connect to a table 2. step through each of its records 3. read the value of two...
2
by: Thelma Lubkin | last post by:
My ColorSet building form/subform now works beautifully, thanks to the help that I've gotten from people in this group. The working form displays the parent ColorSet record with the child records...
9
by: shalskedar | last post by:
Access can store how many maximum number of records for a db.. Wanted to know what is the limitation of total number of records that Access db can store
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.