Compacting

[Guest]

I have a database that uses and discards data from linked and exported
tables. When it does it grows. In an attempt to keep the file size down I
compact and repair. I wanted to do this when the database closes, but when I
use the compact in a macro or in VBA I get a message that says it cannot
compact while running VBA.

Is there a way to accomplish this task of compacting maybe at another time?

Matthew

 

[Dirk Goldgar]

I have a database that uses and discards data from linked and exported
tables. When it does it grows. In an attempt to keep the file size
down I compact and repair. I wanted to do this when the database
closes, but when I use the compact in a macro or in VBA I get a
message that says it cannot compact while running VBA.

Is there a way to accomplish this task of compacting maybe at another
time?

Matthew

What version of Access are you using? In Access 2000 or later, you can
set the Compact On Close option to cause a database to be compacted
automatically whenever the last user who has it open closes it.

 

[Guest]

Thanks for the response. I am using access 2003. I just had it installed.
I am not sure what you mean. I tried a macro and referred to that macro in
the on close section of my form. It gave me the message so I tried to call
the macro from vbcode and I got the same message. How do I set the compact
on close?

Matthew

 

[Dirk Goldgar]

Thanks for the response. I am using access 2003. I just had it
installed. I am not sure what you mean. I tried a macro and
referred to that macro in the on close section of my form. It gave
me the message so I tried to call the macro from vbcode and I got the
same message. How do I set the compact on close?

With the database open in Access, click Tools -> Options... on the menu
bar, go to the General tab of the Options dialog, and check the box
labeled "Compact on Close". Click OK to save the changed option, and
you're all set. The database will automatically be compacted when you
close it. If multiple users have it open at once, it will be compacted
when the last user closes it.