Compress and Repair Once per month

[NewbieUK]

Hi Guys,

Could do with some help here, not sure how to go about this. I'm a beginner at VBA and this is a little out of my league!! Been seraching for a while but found nothing so far that could help me.

I would like my database to conpact and repair once per month automatically, however, the database runs on Windows Scheduler and uses an Autoexec macro each day. What I was going to do is get the macro to run the Compress code but only on the 1st day of each month. Only problem with that is, when it re-opens after the compacting, it will go to run the autoexec again, and start the compact again!!!

Any ideas? any help much appreciated.

Thanks

 

[dbDamo]

Only problem with that is, when it re-opens after the compacting, it will go to run the autoexec again, and start the compact again!!!


Thanks

Not if you store the date of the last compact and repair, then check the current date against the stored date before running it again.

i.e. your AutoExec code would

1. Check the date to see if its the 1st of the month, if it isn't then it exits the procedure

2. If it is then it checks the table that you store your last compact date in and checks that date against the current date

3. If the current date is equal to the date held in the table then it exits the procedure

4. If the current date and date held in the table do not match, then set the value in the table to equal the current date and run the compact and repair

 

[namliam]

If it is scheduled to run anyways, why not simply compact on every use?

Tick the compact on close option, thus it will compact everytime it closes.

Tools> Options > General tab > Compact on Close

 

[namliam]

Alternatively create a seperate schedule that runs on the 1st of the month to execute a compact only:
msaccess c:\YourDatabase.mdb /compact

That wont open the DB, but only compact it.

Oh, and welcome to AWF

 

[ghudson]

You can easily turn on the compact on close option when ever you want. Run this each time your database is opened and it will turn on the compact on close option when the database is opened on the last day of the month.

If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
    Application.SetOption ("Auto Compact"), -1
Else
    Application.SetOption ("Auto Compact"), 0
End If

 

[NewbieUK]

Thanks so much for all your suggestions! Been a great help!!!