Automate compacting an access database and opening it again

[Guest]

Can someone help me with an Access 2003 problem? I have an Access database
which needs to be able to automatically compact itself after deleting the
records in five of it's tables via macros. Upon completion, the automated
process needs to continue processing the remaining macros which includes
importing the newest data, editing the data and exporting the information in
text format.

 

[Geoff]

Eric,

I have an Access database which needs to be able
to automatically compact itself...

As far as I am aware, a database cannot automatically compact itself - that
is, you can't write a program to compact an open database. This may seem
strange because you can compact an open database from the user-interface
(Tools > Database Utilities).

However, you can write a program to compact a closed database. I'm afraid I
don't know whether you can write such a program using macros; but you can
write such a program using VBA. Can you use VBA instead of macros? If so,
you could complete all your programming tasks in the different database,
including opening your existing database to delete data from the five
tables, closing the database to compact it, and opening it again to import,
edit and export the newest data.

Geoff.

 

[Steve Schapel]

Eric,

Can you explain why you need to do this? Just seems like an unusual
requirement.

You can use this VBA code...
CommandBars("Menu Bar").Controls("Tools").Controls("Database
Utilities").Controls("Compact and repair database...").accDoDefaultAction

If you want to stick with your macros process, you could write the above
code into a function in a standard module, and then use a RunCode action
in the appropriate spot in your macro.