Large Database will not compact

[eastcoasttech]

I have a access database (2000 format) that is approaching 700mb. I've
tried running the compact and repair utility provided with access which gets
to the 6th notch on the progress bar and after many hours never moves. I've
also tried running the jetcomp.exe utility which crashes with the following
error report.
AppName: jetcomp.exe AppVer: 4.0.1532.0 ModName: msjet40.dll
ModVer: 4.0.8618.0 Offset: 0004e16f

Im concerned with the performance and stability of the database
If anyone knows any tips or tricks to get the compact to run I'd appreciate
it.

 

[Tom Wickerath]

Hi Eastcoasttech,

Try creating 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
__________________________________________

 

[Allen Browne]

1. Create a new (blank) database.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect

3. Import everything from the old database:
File | Get External | Import

 

[Tony Toews [MVP]]

I have a access database (2000 format) that is approaching 700mb. I've
tried running the compact and repair utility provided with access which gets
to the 6th notch on the progress bar and after many hours never moves. I've
also tried running the jetcomp.exe utility which crashes with the following
error report.
AppName: jetcomp.exe AppVer: 4.0.1532.0 ModName: msjet40.dll
ModVer: 4.0.8618.0 Offset: 0004e16f

To add to the other replies. You almost certainly will want to import
the layout of the relationships window once you are finished with a
successful import into a new MDB.

Use the sysrels utility at the following location to copy the table
relationships layout window
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12
Or use Save Restore Modify Relationship Window at
http://www.lebans.com/saverelationshipview.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[eastcoasttech]

i attempted to import the records as instructed to a new database which
failed. the good thing however is it consistently failed on the same table.
after removing a record from the table that had bad data i was able to run
the compact and repair bringing my db to 160mb. thank you to all who
responded.