2 Gig Limit

[Mike C]

Does anyone know if there is a way around the 2 gig limit?

I'm using Access 2000 and I was wondering if you're allowed over the 2 gig
limit in later versions of Access and/or if Microsoft is planning to increase
the size limit.

 

[Tom van Stiphout]

On Wed, 24 Mar 2010 19:38:02 -0700, Mike C

There are no such plans that I am aware of. Even Access 2010 64-bit
edition has this limitation.
You could conceivably place each table in its own database and link to
all those individual tables. <shudder>.
SQL Server Standard Edition would be the natural upgrade path. The
free Express Edition is limited to 4 GB.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

Does anyone know if there is a way around the 2 gig limit?

I'm using Access 2000 and I was wondering if you're allowed over the 2 gig
limit in later versions of Access and/or if Microsoft is planning to increase
the size limit.

Not through A2010, to my knowledge. A2007 handles image data much more
efficiently than 2000 did, so that might be a solution if images are the
source of your problem.

If you really have 2GByte of actual data (i.e. not a lot of pictures or binary
objects) then you should certainly be considering a client/server backend. SQL
Express is free and doubles that limit; MySQL and other c/s engines are
available as well. With some hassle, you can go to multiple backend databases
(though you cannot enforce referential integrity across databases, only within
them).

Are you storing images? Is your database split into a frontend (forms,
queries, reports, code) and a backend (tables only)? Do you have Word
documents or Excel workbooks in OLE object fields? Other big chunks? Do you in
fact have tens of millions of records in your largest table? Do you compact
regularly?

 

[Mike C]

This is so cool, I'm always flattered when an author of a book I own replies
to a question on these boards.

No images, I am storing years of operational data and somewhere in late 2009
broke the bank. Currently, I have started another database but I am going to
look into SQL Express. Thank you.

 

[John W. Vinson]

This is so cool, I'm always flattered when an author of a book I own replies
to a question on these boards.

I presume you're referring to my friend John Viescas - I own three of his
books too, but I'm not an author myself.

No images, I am storing years of operational data and somewhere in late 2009
broke the bank. Currently, I have started another database but I am going to
look into SQL Express. Thank you.

I'm curious how big (number of records, average size of records) in your
largest tables. There are often questions here about the realistic limits of
Access, and it sounds like you have a real-life test case!