As promised, here is a routine to compact then backup your BE database.
Its a function so it can be run from a macro if needed - see below
The backup file will include the date & time in the format yyyymmddhhnn
Place in a standard module and tweak as appropriate. Omit any parts you don't need
Code:
Option Compare Database
Option Explicit
Public Function BackupBEDatabase()
On Error GoTo Err_Handler
'creates a copy of the backend database to the selected backups folder with date/time suffix
Dim fso As Object
Dim strFilename As String, strFilePath As String, strFileType As String, strBackupsFolder As String
Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
Dim newlength As Long
Dim strPwd As String
strPwd = "Your BE password here"
strFilename = "Your database name here"
strFilePath= "Your database path here"
strBackupsFolder = "Yourbackup folder path here"
strFileType = Mid(strFilename, InStr(strFilename, ".")) 'e.g. .accdb
strOldPath = strFilePath & "\" & strFilename
strNewPath = strBackupsFolder & "\BE\" & _
Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType
strTempPath = strBackupsFolder & "\" & _
Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType
' Debug.Print strOldPath
' Debug.Print strTempPath
' Debug.Print strNewPath
'optional message - omit section if run automatically via scheduled task or similar
If MsgBox("This procedure is used to make a backup copy of the Access back end database." & vbCrLf & _
"The backup will be saved to the Backups folder with date/time suffix" & vbCrLf & _
vbTab & "e.g. " & strNewPath & vbCrLf & vbCrLf & _
"This can be used for recovery in case of problems " & vbCrLf & vbCrLf & _
"Create a backup now?", _
vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
Exit Function
Else
DoEvents
StartBackup:
Set fso = CreateObject("Scripting.FileSystemObject")
'copy database to a temp file
fso.CopyFile strOldPath, strTempPath
Set fso = Nothing
'compact the temp file (with password) - if required
DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & strPwd & "", , ";PWD=" & strPwd & ""
'delete the tempfile
Kill strTempPath
DoEvents
OPTIONAL - 'get size of backup
newlength = FileLen(strNewPath) 'in bytes
'setup string to display file size
If FileLen(strNewPath) < 1024 Then 'less than 1KB
strFileSize = newlength & " bytes"
ElseIf FileLen(strNewPath) < 1024 ^ 2 Then 'less than 1MB
strFileSize = Round((newlength / 1024), 0) & " KB"
ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
Else 'more than 1GB
strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
End If
DoEvents
End If
'Optional success message - omit if run automatically
MsgBox "The Access backend database has been successfully backed up." & vbCrLf & _
"The backup file is called " & vbCrLf & vbTab & strNewPath & vbCrLf & vbCrLf & _
"The file size is " & strFileSize, vbInformation, "Access BE Backup completed"
Exit_Handler:
Exit Function
Err_Handler:
Set fso = Nothing
If Err <> 0 Then
MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & vbCrLf & _
Err.description, vbCritical, "Error copying database"
End If
Resume Exit_Handler
End Function
You can run this at any time from the within the FE itself
However, to run it automatically at a scheduled time there are 3 possible approaches you could use. Choose whichever suits you best.
1. Create a hidden form that runs at startup & is always open
Add a form timer event so it runs hourly - time interval = 3600000 (its in milliseconds)
Add code to the timer event so it runs the backup if close to midnight
Code:
If Hour(Now())=0 Then BackupBEDatabase
2. Create a macro to run the scheduled task. Call the macro e.g. BackupData
Now create a scheduled task to run at midnight, open your FE, run the macro then quit the app
You will need to use your full FE path followed by the command line switch /BackupData
3. Create a small standalone app which includes the backup routine and an autoexec app so it runs at startup.
The macro does the same as in 2. above
Create a scheduled task to run that standalone app at midnight
Method 1 is simplest but requires your app to be left running overnight. Will fail if your app or the hidden form is closed
Method 2 or 3 are more robust but require setting up a scheduled task
My preferred method is usually method 3
Hope that all makes sense.
Let me know if you have any problems