Maximum records in a MS Access 2003 database?

How many records can a Microsoft Access 2003 database handle?

I know there is a 'practical' limit of 2Gb fize size, but for a 4 field per record database can anyone tell me a rough number?  I have between 5 - 10 million records to import.
helpdeskAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
In fact, I've created a test mdb before with 100 mil records to test some index ideas, etc.  AMAZING what indexes can do ... of course, they are a double edged sword!

mx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
5-10 million records with 4 fields is no problem, even for access.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
** Should ** not be a problem.  Ive' done it before.  Waaay too many variables to venture a guess of a number.

mx
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jerryb30Commented:
How big are the fields?  What data types?
What other objects do you have in the db?


0
 
Jeffrey CoachmanMIS LiasonCommented:
helpdesk ,

If you are concerned about size, just make sure to set the smallest possible datatypes for all of your fields.

For example, most people blindly set all number fields as "Long",  (4 bytes), because that is the default Number DataType in Access.
(-2,000,000 to +2,000,000)

By comparison "Integer" is 2 bytes.
-65,000 to +65,000

(most people will set a persons Age as Long.
I use Byte (0-255)
Even with genetic manipulation, I figure 200 years...tops!)

So difference does the 2 byte difference make?
Not much if you only have one hundred records.
...but if you have 10 Million records!... well you do the math!

Also make sure your database is split (Front-end, Back-End)

Jeff Coachman

0
 
GRayLCommented:
A long time ago I was told if you do any math on a field such as a sum, you should make sure the sum does not exceed the limits for the number type as Jet will try to use the Numbertype of the field as a first pass.  When it doesn't 'fit', it has to recast the solution and run it again.  I've never heard anything about this again so I'm not sure it's true.  Can anyone comment?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm just leaving from LA to San Diego ... will be back later with comments.  You guys and girls hold it down, ok lol.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Do we have a resolution to this question?  Just checking ...

mx
0
 
GRayLCommented:
I think the answer was: "It depends".  With a file size limit of 2 Gig, of course it will depend on the record length.  DQOTWA.
0
 
GRayLCommented:
Jeff: "By comparison, Integer is -32,768 to +32767" or maybe the other way round
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.