Auto Backup

[jo15765]

I have a database that users are using around the clock 24/7. I am wanting to "behind the scenes" backup the database. To save the information that they are entering, so if by some freak accident the database is deleted or corrupt. I was wanting to perhaps have the database autosave every 4 hours, but I don't know how to do this.

Any ideas?

[ajetrumpet]

IDK if this would be helpful, but I use it sometimes to backup a database file:

Function BackupSource()

'******************************************************************************
'                                                                             *
'Author: Unknown                                                              *
'Modified By: Adam Evanovich                                                  *
'Date: 9/25/2010                                                              *
'Purpose: To backup the source BackEnd file of a database                     *
'                                                                             *
'Arguments: None                                                              *
'                                                                             *
'******************************************************************************

On Error GoTo Err_BackupSource

Dim strBu As String
   Dim buf As String
      Dim MD_Date As Variant
         Dim fs As Object
            Dim strSourceName As String
               Dim strSourceFile As String

                  Const conPATH_FILE_ACCESS_ERROR = 75

strSourceName = CurrentProject.Name
strSourceFile = CurrentProject.Path
buf = CurrentProject.Path & "\Backups\"

   If GetAttr(buf) <> vbDirectory Then
      MkDir buf
   End If

Continue:

      MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
      strSourceFile = CurrentProject.Path
      strBu = CurrentProject.Path & "\Backups\" & MD_Date & "\"
   
         MkDir (strBu)
         
            Set fs = CreateObject("Scripting.FileSystemObject")
               fs.CopyFile strSourceFile & "\" & strSourceName, strBu
            Set fs = Nothing
 
'Successful
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successful!", _
        vbInformation, "Backup Successful"
 
Exit_BackupSource:
  Exit Function
 
Err_BackupSource:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & strBu & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
      If Err.Number = 53 Then
         MkDir buf
            GoTo Continue
      Else
         MsgBox Err.Description, vbExclamation, "Error Creating " & strBu
      End If
  End If

End Function '//LL

[jo15765]

Code looks like it would work, just 2 questions:
1)Will this code work to backup if users are using the database?
2) Will this auto backup or will I have to tell it to add?

[ajetrumpet]

IT CREATES a subfolder for backups in the same dir as the db. and it works for one user, so it most likely will work for more than one. if you look at the code, it's obvious that it has to be run from the same file that's being backed up. it was not written by me, but it's not complicated. if there's risk, copy your file before running it.

[jo15765]

When does it create the backup?

Would this work?

Create a batch file (mycopy.bat):
copy c:\mydir\myfile.mdb d:\mybackupdir
Place a shortcut to it on your desktop.

[ajetrumpet]

When does it create the backup?

Most likely whenever you run the code. without doing that, I doubt it will be possible!


there is a million ways to do this jo. the code I posted does it, so does your idea of a batch file, so does this:

Function DOScommand(comName As String, _
                    strArg As String)

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 10/22/2010                                                             *
'Purpose: To execute a single DOS command.                                    *
'                                                                             *
'Arguments:                                                                   *
'comName > Name of the DOS command.                                           *
'strArg > Argument syntax required for the DOS command.                       *
'         Example of both arguments are below:                                *
'                                                                             *
'         comName = "COPY"                                                    *
'         strArg = "c:\myTextFile.txt c:\myNewFolder\MyTextFile.txt           *
'                                                                             *
'******************************************************************************

Shell Environ("COMSPEC") & " /C " & comName & " " & strArg & ", 1"

End Function '//LL

and so does this:

Function FileCopy(oFile As String, _
                  nFile As String, _
                  bOverwrite As Boolean)

On Error GoTo Err_Handle

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 11/12/2010                                                             *
'Purpose: Copies a file in windows.                                           *
'                                                                             *
'Arguments:                                                                   *
'oFile > Full path of file to copy.                                           *
'nFile > Full path of new file.                                               *
'bOverwrite > Overwrite old file if new path already exists.                  *
'                                                                             *
'******************************************************************************

Dim r As Long

r = CopyFile(oFile, nFile, IIf(bOverwrite = True, 0, 1))

   If Not (bOverwrite) And r = 0 Then
      MsgBox "File Already Exists..."
         Exit Function
   End If

MsgBox "File Copied Successfully!"
   Exit Function

Err_Handle:
   MsgBox Err.Description

End Function '//LL
______________________________________________________________________________

DECLARATIONS NEEDED
______________________________________________________________________________
Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" () _
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
 ByVal bFailIfExists As Long) As Long

the easiest one is the best. so pick one and run with it. =)

you can also copy a file with vba using the fso. millions of ways...

[jo15765]

I tried all of these codes, as well as my idea of a batch file and none of them worked?! I don't understand what I did wrong?

[ajetrumpet]

I tried all of these codes, as well as my idea of a batch file and none of them worked?! I don't understand what I did wrong?

whats your code?

[jo15765]

I tried

# Public Sub DBbackup()
#  
# Dim DestPath As String
# Dim SrcPath As String
# Dim fso As Object
#  
# Set fso = CreateObject("Scripting.FileSystemObject")
# SrcPath = CurrentProject.FullName
# DestPath = CurrentProject.Path & "\BDbak" & Format(Now, "yymmddhhnnss") & ".mdb"
#  
# fso.CopyFile SrcPath, DestPath
#  
# Set fso = Nothing
# MsgBox "DB copied ", vbInformation, SBaviso
#  
# End Sub

Private Sub Command150_Click()

Call BackupFrontEnd

CloseCurrentDatabase

End Sub


Public Function BackupFrontEnd()

Dim dbs
Dim tdfs
Dim fso
Dim strCurrentDB
Dim intExtPosition
Dim strExtension
Dim intExtlength
Dim strBackupPath
Dim sfld
Dim strDayPrefix
Dim strSaveName
Dim strProposedSaveName
Dim strTitle
Dim strPrompt
Dim SaveNo
Dim rst

On Error GoTo ErrorHandler
   
   Set dbs = CurrentDb
   Set tdfs = dbs.TableDefs
   'Components of the FileSystemObject object library are used
   'to work with files
   Set fso = CreateObject("Scripting.FileSystemObject")
   strCurrentDB = Application.CurrentProject.Name
   Debug.Print "Current db: " & strCurrentDB
   intExtPosition = InStr(strCurrentDB, ".")
   strExtension = Mid(strCurrentDB, intExtPosition)
   intExtlength = Len(strExtension)
   
   'Create backup path string (Backups folder under database folder)
   strBackupPath = Application.CurrentProject.Path & "\Backups\"
   Debug.Print "Backup path: " & strBackupPath
   
   'Check whether path is valid
On Error Resume Next
   
   Set sfld = fso.GetFolder(strBackupPath)
   If sfld Is Nothing Then
      'Create folder
      Set sfld = fso.CreateFolder(strBackupPath)
   End If
   
On Error GoTo ErrorHandler
   'Create proposed save name for backup
   strDayPrefix = Format(Date, "d-mmm-yyyy")
   strSaveName = Left(strCurrentDB, _
      Len(strCurrentDB) - intExtlength) & " Copy " & SaveNo _
      & ", " & strDayPrefix & strExtension
   strProposedSaveName = strBackupPath & strSaveName
   Debug.Print "Backup save name: " & strProposedSaveName
   strTitle = "Database backup"
   strPrompt = "Save database to " & strProposedSaveName & "?"
   strSaveName = Nz(InputBox(prompt:=strPrompt, _
      Title:=strTitle, Default:=strProposedSaveName))
   
   'Deal with user canceling out of the InputBox
   If strSaveName = "" Then
      GoTo ErrorHandlerExit
   End If
   
   Set rst = dbs.OpenRecordset("zstblBackupInfo")
   With rst
      .AddNew
      ![SaveDate] = Format(Date, "d-mmm-yyyy")
      ![SaveNumber] = SaveNo
      .Update
      .Close
   End With

   fso.CopyFile Source:=CurrentDb.Name, _
      destination:=strSaveName
      Msgbox "A backup with todays date should have been saved in the backups subfolder"
   
ErrorHandlerExit:

   Exit Function

ErrorHandler:
   Msgbox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Function

And also this one....

Create a batch file (mycopy.bat):
copy c:\mydir\myfile.mdb d:\mybackupdir
Place a shortcut to it on your desktop.

[ajetrumpet]

I'm not sure what you're attempting to do jo, but this is a very simple task. for one thing, the following line should error because you don't have a "\" between the subdir and the "format()" code:

# DestPath = CurrentProject.Path & "\BDbak" & Format(Now, "yymmddhhnnss") & ".mdb"

why are you posting code that's supposed to be in the vb editor, but written in a c++ context? If I remember right, preceedings with "#" signs is 'c+' language.


I can't help much now. I've given as much help here as I can. One alternative I'll leave you with is this: http://www.ajenterprisesonline.com/_...fname=filecopy

That is again, yet another example of how to perform this simple task in access. good luck sir!

[Randy]

I use a batch file and windows schedular to backup my database nightly. You could create a batch file similar to the one you are suggesting and use the windows schedular to implememt it at what ever interval you wish.

It shouldn't make any differance whether the file is in use or not because you are just making a copy of the file at that exact time.

Hope this will help.

When does it create the backup?

Would this work?

Create a batch file (mycopy.bat):
copy c:\mydir\myfile.mdb d:\mybackupdir
Place a shortcut to it on your desktop.

[jo15765]

Randy, when I ran the batch file, nothing happened? Did I type the commands wrong?

Also, running windows scheduler is not an option I am on a network with very stringent security settings, and I know I am unable to access that option.

[jo15765]

ajettrumpet ---
Thanks for your valuable input! I did not know that the # signs meant that it was C++ code, I just found it from googling. Again thank you for your assistance.

[Randy]

Put the batch file in the backup directory and reference the full path in a line in that batch file, ie; copy c:\foldername\filename.mdb. Your network security settings might be the problem, your batch file should have worked. Here is a simple batch file example to make sure we are on the same page.

***
echo off
cls
copy c:\myfolder\mysubfolder\mydatabasefile.mdb (substitute the real paht here of course)
exit
***
Place the batch file in the backup directory.

Should work across the local drives on the pc that contains the mdb file. If you are using a network drive as the backup then you will need to use the full network path as the copy directroy.

Hope this helps.

Randy, when I ran the batch file, nothing happened? Did I type the commands wrong?

Also, running windows scheduler is not an option I am on a network with very stringent security settings, and I know I am unable to access that option.

[jo15765]

hmmm....maybe that was the issue, I Did not have the backup file in the backup directory. I am wanting to backup to a network location, and the database I am wanting to backup is on a network location as well. So will I need to include full file paths for both of these in order for the batch file to work?


Also, is there a way to set this to automatically run? I don't think there is without a scheduler etc., but it would be great for it to auto back up, instead of me having to run this .bat file every morning/afternoon. I think this code would do it, if it will work with the security in place...

Public Sub Backup()
Dim MyObject As Scripting.FileSystemObject
Set MyObject = New Scripting.FileSystemObject
 
Dim strDate As String
Dim strDB As String
Dim strDBReplace As String
Dim strDBName As String
Dim strDBPath As String
Dim strBUDB As String
 
strDB = CurrentDb.Name
 
strDBName = Dir(strDB) 'full DB path and name
strDBPath = Replace(strDB, strDBName, "") 'remove DB name from path
 
strDate = Replace(Date, "/", "_") 'underscore date
strDBReplace = Replace(strDBName, ".mdb", ("_" & strDate)) 'add date
to DB name
strBUDB = strDBPath & "backup\" & strDBReplace 'path + backup dir +
new name
 
MyObject.CopyFile strDB, strBUDB, True
 
End Sub

I was curious if this would work, I found this on another website, but wasn't sure if it would work since the other codes, I tried didn't either......