A 2Gig file size?

[NetworkTrade]

I was surprised to see an Access2000 file at 2Gig size - was wondering if
anyone else had experience in this type situation.....

Am told by user that it had maxed out during useage earlier this year - -
giving them some sort of error message which he did not remember the
details....then they had deleted old records and continued using it. They
never looked at the file size and don't know what it was then vs now....

The file size it appears to be still at the max - or extremely close to it.

It is not a split database.

My question is whether it is safe to compress/repair it - - or if it is
highly fragile that I should instead copy it first - and then compress/repair
the copy. Or whether it doesn't matter - - just not sure if a compress
process could create a problem for a maxed out file....

Would definitely welcome experienced advice on this situation. thanx.

 

[Tom Wickerath]

I would definately make a back up copy first, before attempting a compact and
repair operation. That way, if anything does go wrong, you can easily restore
to the original. Does this database include a lot of pictures or other OLE
objects that were added using OLE Embedding technology? That type of thing is
known to cause severe database bloat. Here are a couple of links with to
articles dealing with the subject of DB bloat:

Database bloat is not stopped by compacting database with Access 2002
format
http://support.microsoft.com/?id=810415

http://tinyurl.com/2dmpw


Probably the best solution in this case would be to create a brand new
database, and import all objects from the 2 GB database into this new
database. Here is my boilerplate text on this process:

Create a brand new database and immediately disable the NameAutocorrupt
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's always
best to recreate the linked tables from scratch. When importing local tables,
make sure to check the option to import relationships, menus and toolbars,
and import/export specs. If any of the local tables in the source DB are
hidden, you'll need to first unhide them. You will need to set the checked
references to match the source database, along with any startup options set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system tables
(the tables whose names start with "MSYS"). These system tables are updated
appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group at a
time, re-establishing any linked tables, setting startup options, and setting
references to match the source DB is usually a fairly quick procedure. When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Arvin Meyer [MVP]]

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group
at a
time, re-establishing any linked tables, setting startup options, and
setting
references to match the source DB is usually a fairly quick procedure.
When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.

And 1 more thing in addition to Tom's suggestions. Make sure the database
gets split into a front-end and back-end. That is important even in a single
use database because it lessens the chance of corruption and makes it easier
to make changes to the front-end because you can have multiple front-ends
connected to the same back-end.

 

[NetworkTrade]

ok - thanks for both replies;

don't think images are in db

will copy and then compress the copy - and note new size

will then split copy - and note split sizes

these two things can be done quickly.....

will read up on the links you provide and then determine if bringing all
into a new container is what is going to be needed....

thanks....