Scheduling/automating Compact and Repair

[ppataki]

Dear All,

Is it possible (if yes, how) to run Compact and repair at a given time every day automatically? (so to somehow schedule it?)
Or the other option would be to compact it when the last user closes the db
I have already tried Tools=> Options => General => Compact on close
but it did not work ....

Many thanks in advance

 

[marianne]

yes it is possible. but why would you want to compact and repair your database as per scheduled. what is someone is still working (meaning writing, reading) in your database. that someone will be disconnected and possibly there will be error while reading and writing. However, if you know that no one will work on your database during that schedule compacting then I guess it will be alright.


if you already know what is the code for compacting database then you should keep a time schedule in one table in your database. then using dlookup, you get the value of date in your table and comparing it to the computer time(), then automatically it will do your code.

 

[ppataki]

I am sure that at that time nobody will be in the database

Could you please help me with the code as well?

Thank you very much

 

[marianne]

do you know the code for compacting database? if not go to help in access first and study it. you will find it there.

 

[ppataki]

I found the code for compacting!

But unfortunately I could not find the code on how to schedule something...
or to check if nobody is in the database

 

[John Big Booty]

This thread may give you some pointers.

 

[marianne]

study this. hope this is useful for you.

http://support.microsoft.com/?id=158937

 

[marianne]

after studying it. you can always customize the code to whatever suits you.

 

[Extra_Cover]

You could do it using Windows Scheduled Task. When you have selected the database to run put /compact after it.

 

[ppataki]

Dear All,

I have chosen another solution

The db gets compacted when the first user opens it
I found a code that I customized a bit, I post it below FYI

Option Compare Database
Public Function cptdb()
On Local Error GoTo MCError1
Dim stDatabaseName As String
Dim stLastCompacted As String
Dim stMessage As String
Dim stSQl As String
Dim stTimeNow As String
Dim stToday As String
stToday = Format$(Now, "yyyymmdd") 'Note the yyyymmdd format
stLastCompacted = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = 'LastCompacted'")
stDatabaseName = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = 'DatabaseName'")
'--------------------------------------
'- Database already compacted today -
'--------------------------------------
If stLastCompacted >= stToday Then
Exit Function
End If
'--------------------------------------
'- Database compact process is -
'- required. Display message -
'--------------------------------------
stMessage = "You are the first person to use this database today." & vbCrLf & vbCrLf

If intSecurityLevel = 1 Then
stMessage = stMessage & "Please ask someone with Data-entry or Administrator permissions "
stMessage = stMessage & "to log in and run start of day maintenance."
MsgBox stMessage, vbInformation, stDatabaseName
Exit Function
Else
stMessage = stMessage & "When you click [OK], start of day maintenance will take place." & vbCrLf
MsgBox stMessage, vbInformation, stDatabaseName
stMessage = SysCmd(acSysCmdSetStatus, "Daily Maintenance In Progress ... Please Wait")
End If
'---------------------------------------
'- Write a log record -
'---------------------------------------
Rem stMessage = WriteLogRecord("CompactDatabase", "MaintenanceCheck", "")

'---------------------------------------
'- Update the Control Table record -
'---------------------------------------
stSQl = "UPDATE tblControl1 SET [tblControl1].[ParameterValue] = '" & stToday & "' WHERE [tblControl1].[ParameterName] = 'LastCompacted'"
DoCmd.SetWarnings (False)
DoCmd.RunSQL (stSQl)
DoCmd.SetWarnings (True)
'---------------------------------------
'- Call the CompactDatabase function. -
'- This must be the last line of code -
'- in the MaintenanceCheck function -
'---------------------------------------
Call CompactDatabase
Exit Function
MCError1:
MsgBox CStr(Err) & " - " & Error$
Resume MCEnd
MCEnd:
End Function

Public Function CompactDatabase()
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Function

I tested it and seems to be working fine