2 GB size limit fix

[Bob]

Hi all,

My databases seem to become corrupted as soon as they reach 2GB. Once
it does become corrupted the only fix is to create a new database and
transfer everything into that. I need to avoid that happening and I'm
not sure exactly how to do that. Is there a way that I can check the
size of the database (don't know how) and then remove obselete tables
(know how to) if its over a threshold and then compact and repair the
db (know how to)? Or is there a better way to get around or prevent
it?

Cheers,

Bob

['69 Camaro]

Hi, Bob.

My databases seem to become corrupted as soon as they reach 2GB.

Are you compacting the database files regularly? If they're compacted and
they're still close to 2 GB, you should migrate the data to a larger
capacity database. A stop-gap maneuver would be to move some of the tables
to another MDB file, so as to split up the data into two files that each
have a maximum size of 2 GB. The downside to this is that only tables in
the same file can have referential integrity enforced.

Is there a way that I can check the
size of the database (don't know how)

Yes. It can be checked whenever the database file is opened to determine if
the file surpasses a certain size, and if it is, compact it. If it's still
above a certain size, the VBA procedure can display a message to the user
that the file is near maximum capacity and the user needs to do something
about it. For an example of how to check the file size and automatically
compact the database, please see the tip, "How to compact the current
database automatically when it reaches a certain size," on the following Web
page:

http://www.access.qbuilt.com/html/vba1.htm

Or is there a better way to get around or prevent
it?

Consider migrating the data to a client/server database engine. There are
several free ones from the major database vendors, such as Oracle 10g
Express and SQL Server 2005 Express, which will hold up to 4 GB of data.
Other free ones hold even more. Please see the following Web page for links
to these database engine descriptions to compare their major features and
additional links to each vendor's download page:

http://www.backends.qbuilt.com/

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[Tom van Stiphout]

On 17 May 2007 19:12:05 -0700, Bob
<sc*******@colonialfirststate.com.auwrote:

Obsolete tables can be removed at any time; you don't have to wait
until your file size is too high.
You can test the size of a file many different ways. Have you tried
the built-in FileLen function?

If you regularly get up to that limit, and have an otherwise good
database design, it may be a good time to invest in SQL Server or
another database with much larger capacity.

-Tom.

>Hi all,

My databases seem to become corrupted as soon as they reach 2GB. Once
it does become corrupted the only fix is to create a new database and
transfer everything into that. I need to avoid that happening and I'm
not sure exactly how to do that. Is there a way that I can check the
size of the database (don't know how) and then remove obselete tables
(know how to) if its over a threshold and then compact and repair the
db (know how to)? Or is there a better way to get around or prevent
it?

Cheers,

Bob