Compact and Repair

[Lyndseyd]

Hi all,

This morning i opened up my database, it worked fine, ie could add new records on to the forms etc but when i went to look at my tables, queries, forms etc nothing was there and an error message about indexes came up.

I had a look through this forum and found a lot of useful information on compacting and repair. I compacted my database and it went from 89KB to creating a new database with only 28KB. The new one which it created works perfect for me and i have deleted my old one, as still no tables, queries etc showed up.

I didn't even have a clue i should have been compacting my database regularly, stupid i know but we all learn. My question is, when looking at threads i have noticed that when people talk about compacting, repair is mentioned too, do i need to run the repair tool now on my database? Or is it ok. Im not entirely sure of its function, i will keep looking though, Thanks for any replys

 

[Ron_dK]

...talk about compacting, repair is mentioned too, do i need to run the repair tool now on my database?

Repair is included in the compact utility tool. Most users do a compact on exit
the Dbase.
Be sure that you maintain a back up of your application at all times.
Note that if you are compacting a shared database that is located on a server or shared folder, ensure that none of the users has it open.

 

[Lyndseyd]

Thanks for your help rak. I do do regular backups of my database already, the reason i never used an old copy of my database when this error happened, was i was trying to figure out how i could solve it and was wondering what the compact would do. Thanks for your advise.

 

[Ron_dK]

Hi Lindsey,

Maybe this might give you some idea on what compact does :

http://www.trigeminal.com/usenet/usenet023.asp?1033

 

[Lyndseyd]

Thats great, things are much clearer now. Thanks

 

[ghudson]

The repair function is built into the compact funtion in Access 2003. It was a seperate function in Access 97.

The Microsoft Jet Compact Utility is great for fixing a database that is damaged because of corrupt indexes. Search around for I have numerous threads about the free utility and where to find it.

 

[Lyndseyd]

Yes Ghudson, i had read a lot of your useful threads this morning about compact and repair and did downloaded the Microsoft Jet Compact Utility for free. For some reason is just kept saying that their was an error when i was trying to compact it. So i just then used the compact function in Access. I am using Access 97, so would you recommend i click on repair function now? Also what will this actually do as my database is working fine now. Thank you

 

[alastair69]

What Compacting does and how it works

Dear Lyndseyd,

I have read your question carefully and i understand your problem.

The compact option will remove all the old forms and data from your database that you have deleted while working on the DB, also it removes any problems with data this can be some thing like autonumbers jumping which is a common problem i have found, also some times the DB can get a bottle neck from other users on a network as each time a user opens your data file all the information is set to that PC and if you think of perhaps 10 users that would be 10 copies of your database been opened unless you are using SQL.

The best way of working would be to use the compact once a week maybe twice a week if the DB is used a lot, use can use the following short cut to access the DB.

1. Create a new short cut on your decktop
2. Name it as something you can understand what is is doing "Compact (Your DB Name.
3. copy the following code in to the target file "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "YOUR DATABASE LOCATION .mdb" /compact /Repair

This will mean you should not have to go into the database each time and you can set it up to automatically run once a week or at a certan time of day.

I hope this helps you out and if not that let me know and i will try and make it a bit easier to understand.

 

[Lyndseyd]

would i have to set this to run using the scheduled task on windows? Thanks for your help by the way

 

[alastair69]

yes you would need to setup a scheduled task on windows

 

[Lyndseyd]

Oh i thought so, unfortunately the company i work for has blocked all the users from running scheduled tasks, so running this compact is going to be a problem for me. I usually am in later and finish earlier than the users of the database, so not sure what i will do, il sort something though, thanks for your help

 

[TanisAgain]

Lyndsey, you don't have to compact through the Windows scheduler. You can compact and repair at scheduled times through Access. Have a look here.

http://support.microsoft.com/kb/209979/en-us