ACCESS 2007 DATABASE SIZE

[Jim]

Is there a database size limit for Access 2007 or is it determined by
storage space?

 

[John Spencer]

As far as I know, the size limit is 2 Gigs if you are using Jet or ACE as the
data repository. If you are using MS SQL server, then the limits are those
for your version of MS SQL server. If you are using Oracle or MySql or ...
then the size limits are governed by those applications.

However if you choose to split the data into multiple Access (Jet or ACE)
backends you can in theory have really large databases (up to 2 gigabytes for
each of the linked backends). I suspect that the performance could (probably
would) be terrible if you needed to do this.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

However if you choose to split the data into multiple Access (Jet or ACE)
backends you can in theory have really large databases (up to 2 gigabytes
for each of the linked backends). I suspect that the performance could
(probably would) be terrible if you needed to do this.

Of course, you wouldn't be able to set relations between tables in different
backends.

 

[Steve]

To clarify ..........

You would be able to set relationships between tables in a backend. You
wouldn't be able to set relationships between tables in different backends.

Steve
(e-mail address removed)

 

[Douglas J. Steele]

And that's different than what I said how...?

You used the exact same words as me, Steve! "You wouldn't be able to set
relationships between tables in different backends."

 

[Arvin Meyer [MVP]]

However if you choose to split the data into multiple Access (Jet or ACE)
backends you can in theory have really large databases (up to 2 gigabytes
for each of the linked backends). I suspect that the performance could
(probably would) be terrible if you needed to do this.

Not as bad as one would think. In the huge 30 GB Access 2.0 database I once
worked on, a list box selection named the table, and a record could be
retrieved in a few seconds.

In an Access 2003 database with 2, 1+ GB backends, joined with a union
query. A record could be retrieved from over 12 million rows in about 20
seconds. Considering that it had to completely run the union query first,
that isn't too bad.