Limits of MS Access 2003

[Joe Chambers]

A quick question: What are the capacity limits of MS Access 2003?

Background: We are using MS Access at our company and I believe our
database has grown to 9 Gb. I'm being told that we are near the limits of
MS Access. In fact I was referred to a web page
(http://www.earthskater.net/devzone/access-specifications.asp - not
Microsoft's) that states the limits for MS Access 2003 as 2 Gb. So I wonder
if we really have 9 Gb worth of data in the MS Access database or if the
website is actually wrong.

-- Joe

 

[OceanView]

A quick question: What are the capacity limits of MS Access 2003?

Background: We are using MS Access at our company and I believe our
database has grown to 9 Gb. I'm being told that we are near the
limits of MS Access. In fact I was referred to a web page
(http://www.earthskater.net/devzone/access-specifications.asp - not
Microsoft's) that states the limits for MS Access 2003 as 2 Gb. So I
wonder if we really have 9 Gb worth of data in the MS Access database
or if the website is actually wrong.

-- Joe

I can't say for sure what the size limits are, but I'm surprised you
didn't hit it already if you're using the Jet database engine. You're
approaching what I'd call the 'practical' limits. I would certainly split
the tables out and convert to another backend capable of handling larger
amounts of data if it were my database. SQL Server is the first choice
since Microsoft likes Microsoft. I think even the free SQL server
Express (or ehatever it's called this week) would be an upgrade. Just my
opinion.

 

[Sylvain Lafontaine]

Either you are wrong about the size of your database or it's split into
multiple databases or it's using something else - like SQL-Server - for the
storage of the data. To overcomes somes of the limitation of JET,
SQL-Server is often used as a replacement to JET for the storage of data and
Access is then only used as the frontend (or interface) for the databases
stored on SQL-Server.

 

[John W. Vinson]

A quick question: What are the capacity limits of MS Access 2003?

Background: We are using MS Access at our company and I believe our
database has grown to 9 Gb. I'm being told that we are near the limits of
MS Access. In fact I was referred to a web page
(http://www.earthskater.net/devzone/access-specifications.asp - not
Microsoft's) that states the limits for MS Access 2003 as 2 Gb. So I wonder
if we really have 9 Gb worth of data in the MS Access database or if the
website is actually wrong.

-- Joe

Something's screwy here. An Access JET .mdb file is limited to 2 GBytes. I'm
quite certain that your 9 GByte database is something else... or is cruising
for a big crash right away.

Might your database be a linked file in some other software, such as
SQL/Server? Or are you summing the sizes of several .mdb or .mde files?

John W. Vinson [MVP]

 

[Joe Chambers]

Thanks for all of your responses!

I had our PC support person go back and confirm his findings of a 9 Gb MS
Access database. He was actually mistaken and the database is only 900 Mb
(just under 1 Gb). I guess that we don't have a crisis yet.

Again, I want to thank everybody for the good information.

-- Joe

 

[OceanView]

Something's screwy here. An Access JET .mdb file is limited to 2
GBytes. I'm quite certain that your 9 GByte database is something
else... or is cruising for a big crash right away.

Might your database be a linked file in some other software, such as
SQL/Server? Or are you summing the sizes of several .mdb or .mde
files?

John W. Vinson [MVP]

Could he be seeing an uncompressed size? I'm guessing that some people
don't know you need to compress periodically! Even still, I can't image
an Access DB that big.

 

[OceanView]

Thanks for all of your responses!

I had our PC support person go back and confirm his findings of a 9 Gb
MS Access database. He was actually mistaken and the database is only
900 Mb (just under 1 Gb). I guess that we don't have a crisis yet.

Again, I want to thank everybody for the good information.

-- Joe

That's good! I'd start planning, though, because the crisis isn't that
far off!

 

[John W. Vinson]

I had our PC support person go back and confirm his findings of a 9 Gb MS
Access database. He was actually mistaken and the database is only 900 Mb
(just under 1 Gb). I guess that we don't have a crisis yet.

<chuckle> Well, only an order of magnitude off... what's a gigabyte or ten
between friends!

Still, that's getting *very* big for an Access database. You'll need to keep
good backups, compact regularly, and pay close attention to indexes and good
query design (all good things for any size database of course). Are you
storing images in the tables? If so be aware that (up until A2007) they are
handled VERY inefficiently. If you have 900MByte of actual text data, then you
should be very seriously considering a client-server solution such as
SQL/Server (the Express version can save you a bundle).

John W. Vinson [MVP]

 

[Joe Chambers]

Thank you sir. I'll pass this on to our support staff.

 

[David W. Fenton]

Could he be seeing an uncompressed size? I'm guessing that some
people don't know you need to compress periodically! Even still,
I can't image an Access DB that big.

No. As soon as a Jet MDB crosses the 2GB limit, it's corrupted.
Compacting usually won't fix it (i.e., there will most likely be
data loss).

 

[David W. Fenton]

I had our PC support person go back and confirm his findings of a
9 Gb MS Access database. He was actually mistaken and the
database is only 900 Mb (just under 1 Gb). I guess that we don't
have a crisis yet.

If that's uncompacted, it's probably not an issue in the long run.
If it's the compacted size, I'd say you should probably be planning
to upsize.

And either way, I'd put in place a daily archive and compact
operation. The FMS Agent software is very good for this and now can
be run as a system service.