How to compact databases

[Jan Nademlejnsky]

I have several databases in different directories. I would like to create a
macro which would open 1st database, compact it and then save it, then
repeat the same for the next database and so on.

Let's assume this type structure:

c:\data1\test1.mdb
c:\data2\test2.mdb
c:\data3\test3.mdb

Can somebody help me, please.

Thanks

Jan

 

[Steve Schapel]

Jan

This is very difficult to achieve using a macro. The
RunCommand/CompactDatabase macro action requires user input for the
input and output files. It is much easier to do with a VBA procedure,
for example...
DBEngine.CompactDatabase "C:\Data1\test1.mdb", "C:\Data1\test1_bu.mdb"
Kill "C:\Data1\test1.mdb"
Name "C:\Data1\test1_bu.mdb" As "C:\Data1\test1.mdb"

 

[Chris]

Try this...

http://www.peterssoftware.com/cal.htm

I use to use it at my last job and ran it every night before I left office.
I would compact/repair about 8-10 very large db's in about 1 hr. It keeps a
list of what db's you want to compact/repair, when they were last
compacted/repaired, and any errors encountered during process. Great tool.
This guys site has lots of good stuff for Access like this one
(http://www.peterssoftware.com/c_emfa.htm) for sending e-mail from Access
(good for notifying users of completion of process or refresh completed for
reporting). Hope these help.

 

[Jan Nademlejnsky]

Thank you very much. I will explore both suggestions.

Jan