How to backup database in Access automatically...

[cavery]

Hello folks,
Does anyone have software that can automatically backup files on a daily basis? Besides using the scheduler, there is a neat and most importantly free application that does exist, but I've lost the internet address.

Thanks,
Clark

~Clark

 

[Quehay]

I do this using the File System Object (referencing to Microsoft Scripting Library needed (Scripting 1.0 C:\WINDOWS\System32\scrrun.dll). Here's a routine that's aimed at linked tables in one separate mdb file (it's easier still if you just copy current mdb with inboard tables).

[tt]Public Function MakeBackupCopy(Optional v_Context As String, _
Optional v_BackupPath As String) As Boolean
On Error GoTo Error_MakeBackupCopy

Dim strBEFile As String
Dim strCrntFile As String
Dim strBEPath As String
Dim strTargetFolderPath As String
Dim strTargetFile As String
Dim FSO As New FileSystemObject
Dim File As File
Dim TDF As DAO.TableDef

MakeBackupCopy = False 'Set default
'Use existing FE filename + date tag for TargetFile
strTargetFile = CurrentProject.NAME
'Remove File Extension
strTargetFile = Left(strTargetFile, (Len(strTargetFile) - 4))
strTargetFile = strTargetFile & "_BAK" & ".mdb"
'GetBackEnd Path
For Each TDF In CurrentDb.TableDefs
If Len(TDF.Connect) Then
strBEFile = TDF.Connect
'due to password in Connect string there are 2 '=' so
'use Instr Reverse
strBEFile = Mid(strBEFile, (InStrRev(strBEFile, "=&quot + 1))
strBEPath = Left(strBEFile, ((InStrRev(strBEFile, "\&quot - 1)))
'One table is sufficient
Exit For
End If
Next
If Len(v_BackupPath) Then
strTargetFolderPath = v_BackupPath
Else
strTargetFolderPath = strBEPath
End If
strTargetFolderPath = strTargetFolderPath & "\Backup"
If Not (FSO.FolderExists(strTargetFolderPath)) Then
FSO.CreateFolder strTargetFolderPath
End If
strTargetFile = strTargetFolderPath & "\" & strTargetFile
FSO.CopyFile strBEFile, strTargetFile
'If you got here then it worked
MakeBackupCopy = True
If v_Context = "NoMessage" Then 'Do Nothing
Else
MsgBox "Backup Completed", vbInformation, "BACKUP COMPLETE"
End If

Exit_Error_MakeBackupCopy:
Set TDF = Nothing
Set File = Nothing
Set FSO = Nothing
Exit Function

Error_MakeBackupCopy:
MakeBackupCopy = False
RespondToError "MakeBackupCopy", Err.Number, Err.Description, "Backup Failed"
Resume Exit_Error_MakeBackupCopy

End Function[/tt]


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[Quehay]

Sorry I submitted before commenting...

You can take the approach of archiving, where you put a date tag on each backup copy and make a number of copies (then you need to purge them occasionally) or just a most current backup that has the same name--in which case you overwrite the existing copy each time (which is default behavior of FSO.Copy).

There are references to other procs in this so you'll need to remove those. Let me know if you'd like any of this unpacked further.


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[Quehay]

Sometimes I think those who post solutions benefit the most...here's an improved version of the above.

It will take 1. Backup Path parameter 2. Use linked table path or 3. Use Front End path in that order of preference

It's a boolean function so you can use its return to trigger other things if desired (warnings, etc.).

It has a boolean parameter for alerting upon successful completion (failure is always announced in this version).

[tt]Public Function MakeBackupCopy(p_Archive As Boolean, p_Alert As Boolean, _
Optional p_BackupPath As String) As Boolean

On Error GoTo Error_MakeBackupCopy

Const DATE_FORMAT As String = "_mmm-dd-yy-hhmmampm"
Const BACKUP_FOLDER As String = "\Backup"
Const BACKSLASH As String = "\"
Const UNDERSCORE As String = "_"
Const MSGBOX_HEADER as String = "Your App Name"
Dim strSourceFile As String
Dim strSourcePath As String
Dim strSourceFileWithPath As String
Dim strDateTag As String
Dim strTargetFile As String
Dim strTargetPath As String
Dim strTargetFileWithPath As String
Dim FSO As New FileSystemObject
Dim TDF As DAO.TableDef

MakeBackupCopy = False 'Set default

'SOURCE SECTION
If Len(p_BackupPath) Then
strTargetPath = p_BackupPath
Else
strSourceFile = CurrentProject.NAME 'Default value of Front End file
strSourcePath = CurrentProject.Path 'Default value of Front End path
'GetBackEnd Path of linked tables overwrite BEPath
For Each TDF In CurrentDb.TableDefs
If Len(TDF.Connect) Then
strSourceFile = TDF.Connect
'if password in Connect string there are 2 ='s so use Instr Reverse
strSourceFile = Mid(strSourceFile, (InStrRev(strSourceFile, "=&quot + 1))
strSourcePath = Left(strSourceFile, ((InStrRev(strSourceFile, "\&quot - 1)))
'One table is sufficient
Exit For
End If
Next

strTargetPath = strSourcePath 'if linked Back End put Backup there

End If 'check for backup path parameter

strSourceFileWithPath = strSourcePath & BACKSLASH & strSourceFile

'TARGET SECTION
strTargetFile = CurrentProject.NAME 'Use existing FE filename + date tag for TargetFile
strTargetFile = Left(strTargetFile, (Len(strTargetFile) - 4)) 'Remove File Extension

If p_Archive Then 'Overwrite one backup copy or archive w/ date tag?
strDateTag = Format(Date, "medium date&quot & UNDERSCORE & Format(Now, "Short Time&quot
strTargetFile = strTargetFile & "_BAK" & UNDERSCORE & strDateTag & ".mdb"
Else
strTargetFile = strTargetFile & "_BAK" & ".mdb"
End If

strTargetPath = strTargetPath & BACKUP_FOLDER
strTargetFileWithPath = strTargetPath & BACKSLASH & strTargetFile

If Not (FSO.FolderExists(strTargetPath)) Then
FSO.CreateFolder strTargetPath
End If

FSO.CopyFile strSourceFileWithPath, strTargetFileWithPath

MakeBackupCopy = True 'If here then it worked

If p_Alert Then
MsgBox "Backup Completed" & vbCrLf & "Backup Copy:" & strTargetFileWithPath, _
vbInformation, MSGBOX_HEADER
End If

Exit_Error_MakeBackupCopy:
Set TDF = Nothing
Set FSO = Nothing
Exit Function

Error_MakeBackupCopy:
MakeBackupCopy = False
RespondToError "MakeBackupCopy", Err.Number, Err.Description, "Backup Failed"
Resume Exit_Error_MakeBackupCopy

End Function

[/tt]


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[lemerle]

What about a Batch file run from Access or Scheduler:

cd c:cd c:\Contract_Costingxcopy c:\Contract_Costing\*.* /s d:\Backup_Contract\ /y/v