Compact & Repair

[dreamz]

Hi

does anyone know how i would make my database compact & repair itself either when i open or close it?


tried to do it via a macro but it says that it can't compact and repair when a macro is running

 

[selenau837]

Hi

does anyone know how i would make my database compact & repair itself either when i open or close it?


tried to do it via a macro but it says that it can't compact and repair when a macro is running

In Access 2000, go to tool, options , General tab, and select compact on close.

HTH

 

[dreamz]

Thanks a Mill

 

[dreamz]

okay sorry, that doesn't work so well.

i need it to compact & repair. just compact on exit doesn't decrease the size of it

Compact & Repair does though

 

[selenau837]

Thanks a Mill

You're welcome!

 

[selenau837]

okay sorry, that doesn't work so well.

i need it to compact & repair. just compact on exit doesn't decrease the size of it

Compact & Repair does though

Hmm, that is news to me. If it doesn't decrease the size, then what is the purpose of compacting it on close?

I am sure one of the other gurus can assist with this then, because I do not know how to code it to compact and repair on close.

I will be watching so I can learn the difference.

 

[dreamz]

i did the tools-options-general-compact on close.

when the db closed, it compacted but the size didn't change.

i opened it again & did the compact and repair and it dropped in size from 96megs to 4megs, which makes me think the compact on close function didn't work.

 

[selenau837]

i did the tools-options-general-compact on close.

when the db closed, it compacted but the size didn't change.

i opened it again & did the compact and repair and it dropped in size from 96megs to 4megs, which makes me think the compact on close function didn't work.

Wow, that is a HUGE difference. It went down 92megs in size. Holy cow!

Anyways, I went and did a compact and repair on some of my back-ups, but it didn't change the size. I am guessing it is because they get compacted each time someone closes it.

I am sure we will find out the answer soon enough.

 

[wazz]

i've just been wondering myself what the difference is b/t the general 'options/compact on close' and 'database utilities/compact and repair'. great if someone can clear that up. i'm also wondering if compact on close could be made a default setting in the future, or if there is some reason for not setting that default.

 

[dreamz]

Wow, that is a HUGE difference. It went down 92megs in size. Holy cow!

Anyways, I went and did a compact and repair on some of my back-ups, but it didn't change the size. I am guessing it is because they get compacted each time someone closes it.

I am sure we will find out the answer soon enough.

thats coz i deleted the contents of my table 1st

 

[selenau837]

thats coz i deleted the contents of my table 1st

Well, that will do it!

 

[alastair69]

You can do a compact from a short cut using

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "D:\SUBFOLDER|Matrix.mdb" /compact /Repair


This is how i compact my Databases.

Alastair

 

[dreamz]

thanks

i'll tell you what the logic (if u can call it that) is behind this.


i've created this db and everything is automated, so in 3 clicks it imports the new files, rund the query's and exports the files.

i need to hand this over to a user to do so i don't have to run it everyweek.

thing is, the db keeps getting bigger and bigger, coz the files that are being imported are big. i don't wanna have to compact & repair every 2weeks, so i thought if i could make the db compact & repair on it's own either on open or close then i won't have to worry about it falling over when it gets too big.

i have tried to do via a macro, but u can't compact & repair when a macro is running.

Only other option i can thing of is adding a exit/close button to my form but making the db compact & repair b4 it closes.

i tried the following in my code but i get the same you cannot compact a database when a macro is running error.

DoCmd.RunCommand (acCmdCompactDatabase)
Docmd.RunCommand (acCmdRepairDatabase)

 

[dreamz]

-*bump*-


ne 1?

 

[RuralGuy]

Hi dreamz,
Have a look at ghudson's post in this thread.