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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Architect / Systems AnalystCommented:
** Should ** not be a problem.  Ive' done it before.  Waaay too many variables to venture a guess of a number.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.