How can I manage 2.0 GB mdb without problems?

[Guest]

We make a db in Access but since the db increase the size almost a 2.0 gb is
down, so we need to compact db every 3 days, there is a patch in order to
Access can manage more then 2.0 GB of size ?

 

[Graham R Seach]

Split the tables out into several backends.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Granny Spitz via AccessMonster.com]

We make a db in Access but since the db increase the size almost a 2.0 gb is
down, so we need to compact db every 3 days, there is a patch in order to
Access can manage more then 2.0 GB of size ?

Another alternative is to use SQL Express as the back end. It's free and it
holds 4 GB, so you can maintain referential integrity on the tables in that 4
GB, even if you decide to expand out with multiple back end files. I think
SQL Express will run up to 16 instances on the same computer, so you have
some room for expansion.

 

[Guest]

Sound good, but somebody told me that there is a MS-access patch in order to
permit more than 2.0 gb. Is it true ?

 

[Rick Brandt]

Sound good, but somebody told me that there is a MS-access patch in
order to permit more than 2.0 gb. Is it true ?

Nope. If you even come close to the limit it's time to move to a server
back end. There are enough free options out there that there is little
reason NOT to.

 

[Granny Spitz via AccessMonster.com]

Sound good, but somebody told me that there is a MS-access patch in order to
permit more than 2.0 gb. Is it true ?

They're mistaken. There's no patch to extend the file size. Access 2000,
2002, and 2003 MDB's can only hold up to 2 GB of data. Access 95 and 97
MDB's can hold up to 1 GB of data. If you need to store more data that one
file can hold, you can create extra MDB files (back ends) as Graham suggested,
place tables in those back ends, and then link to those tables. So if you
had 2 MDB files, you could hold 2 GB in the first file and link to the tables
in the other file, which could also hold 2 GB. That would be a total of 4 GB
of disk space available for your data. But if you really need 2 GB or more,
you should use SQL Express.

 

[John Vinson]

On Wed, 27 Sep 2006 05:55:02 -0700, MS access 2003 <MS access

We make a db in Access but since the db increase the size almost a 2.0 gb is
down, so we need to compact db every 3 days, there is a patch in order to
Access can manage more then 2.0 GB of size ?

In addition to Graham's and Granny Spitz's suggestions, one question:
is this so large because of millions of records of text data? Or do
your tables contain graphical images? If so, Access is *very*
inefficient at handling graphics, and this kind of bloat is very
common. It may be better to store graphics in external .jpg (or other
graphic format) files, and just store the path and filename in your
Access tables.

John W. Vinson[MVP]

 

[Guest]

Yes, we have a large tables with a hundred thousand a records of text data,
we do not record any grapghical images on it. This database is into our
windows 2003 server, running under access 2003 version, additional almost 50
user access simultaneously to this DB.

 

[John Vinson]

Yes, we have a large tables with a hundred thousand a records of text data,
we do not record any grapghical images on it. This database is into our
windows 2003 server, running under access 2003 version, additional almost 50
user access simultaneously to this DB.

I'll agree with Graham, Rick, and Granny Spitz then - it's time to
move to a client/server architecture. SQL/Server Express, full-blown
SQL/Server, MySQL, or the like... you can still keep your Access
frontend, but this is just getting beyond what a desktop application
like Access was ever intended to handle.

John W. Vinson[MVP]