Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Maximum records in a MS Access 2003 database?

Posted on 2007-03-30
11
Medium Priority
?
11,107 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:helpdesk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18825017
5-10 million records with 4 fields is no problem, even for access.
0
 
LVL 75
ID: 18825026
** Should ** not be a problem.  Ive' done it before.  Waaay too many variables to venture a guess of a number.

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 375 total points
ID: 18825044
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 26

Expert Comment

by:jerryb30
ID: 18825131
How big are the fields?  What data types?
What other objects do you have in the db?


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18825342
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18826235
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
 
LVL 75
ID: 18826268
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
 
LVL 75
ID: 18944402
Do we have a resolution to this question?  Just checking ...

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19642767
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
 
LVL 44

Expert Comment

by:GRayL
ID: 21264908
Jeff: "By comparison, Integer is -32,768 to +32767" or maybe the other way round
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question