Not coding, a question I always wanted to know

  • Thread starter Thread starter Joao
  • Start date Start date
There is no maximum number of records in Access 2003 (nor any other version
of Access). The only maximum there is relates to the size of the database
file. In all versions of Access since (and including) Access 2000, the MDB
(or MDE) file cannot exceed 2 Gb.
 
Hi Douglas,
That limits the records, no? Let's see, 8.000.000 records get an Access MDB
file reach the 2.1 Gb... that's limitation. It also applies to the new Access
2007?
 
Joao said:
Hi Douglas,
That limits the records, no? Let's see, 8.000.000 records get an
Access MDB file reach the 2.1 Gb... that's limitation.
Click to expand...

So yes, every version of Access has a maximum number of records it can
support (in tables contained in one native Jet file).

That number is "it depends on the size of each record and overhead for
indexes and such".
It also
applies to the new Access 2007?
Click to expand...

Yes.
 
Yes, there is a limit.

The limit is not dependent on the NUMBER of records, it is dependent on the
size of the data that is stored. The size is affected by indexes, types of
fields, and amount of data stored in each field (not necessarily the field
size). Plus any other objects you have in the database - forms, reports,
queries, VBA code all consume space if you have not split the application into
a front-end and back-end configuration.

For instance, I have a database with several tables in it. One table has
1.000.000 records in it and the size of the database is approximately 54
megabytes. The records in the table with a million records consist of only
one field with long integer values.

On the other hand if I were to store pictures in the database, I would very
quickly run into the 2 gig limit - probably a few thousand records would come
close to hitting the limit.

Just as an experiment I added a million more records to the table and now the
database size is about 76 megabytes. So I can get a very large number of
records into 2 gigabytes


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Thank you!! Nice info u guys!

John Spencer said:
Yes, there is a limit.

The limit is not dependent on the NUMBER of records, it is dependent on the
size of the data that is stored. The size is affected by indexes, types of
fields, and amount of data stored in each field (not necessarily the field
size). Plus any other objects you have in the database - forms, reports,
queries, VBA code all consume space if you have not split the application into
a front-end and back-end configuration.

For instance, I have a database with several tables in it. One table has
1.000.000 records in it and the size of the database is approximately 54
megabytes. The records in the table with a million records consist of only
one field with long integer values.

On the other hand if I were to store pictures in the database, I would very
quickly run into the 2 gig limit - probably a few thousand records would come
close to hitting the limit.

Just as an experiment I added a million more records to the table and now the
database size is about 76 megabytes. So I can get a very large number of
records into 2 gigabytes


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
 
Back
Top