Results 1 to 4 of 4
  1. #1
    Elfear is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    2

    Auto Backup Backend When Opening Frontend

    We're using a split Access DB at our organization and I'm trying to figure out a slick way of automatically backing up the backend when one of the users opens the frontend for the first time in the morning. I found some great code here that does everything I need to do but instead of backing up the frontend I need the macro to grab the backend file. Hopefully I'm allowed to post the code from Mr. Perris' website here:



    Code:
    Option  Compare Database
    Option  Explicit
    ' This  function copies the Access database to a backup
    ' location when the database is  opened.
    ' It does  this only once in any given day.
    '  Author:   Mike Perris - mikeperris.com
    '  Date:     01-Jun-2012
    '  Version:  1.2
    
    Public  Function BackupOnOpen()
    
    ' ***  CHANGE THE FOLLOWING LINE TO MATCH YOUR BACKUP DESTINATION
    Const  BACKUP_PATH = "D:\Access Databases\backups\"
    
    On Error  GoTo BackupOnOpen_Err
    
    Dim  DateOfBackup As Date
    DateOfBackup  = Nz(DLookup("BackupDate", "BackupDetails",  "BackupDate=date()"), 0)
    If  DateOfBackup = date Then
     Exit Function
    End If
    
    Dim  strSourcePath As String
    Dim  strSourceFile As String
    Dim  strBackupFile As String
    strSourcePath  = GetFileName(CurrentDb.Name, False)  '  false means we want pathname
    strSourceFile  = GetFileName(CurrentDb.Name, True)   '  true means we want filename
    strBackupFile  = "BackupDB-" & Format(date, "yyyy-mm-dd") _
    & "_" & Format(Time,  "hhmmss") & "-" & strSourceFile
    
    Dim fso
    Set fso =  CreateObject("Scripting.FileSystemObject")
    fso.CopyFile  strSourcePath & strSourceFile, BACKUP_PATH & strBackupFile, True
    Set fso =  Nothing
    DoCmd.SetWarnings  False
    Dim SQL As  String
    SQL =  "INSERT INTO BackupDetails " _
      &  "(BackupDate, ComputerName, BackupFolder, Filename) " _
      &  "VALUES ('" & date & "', '" &  Environ("COMPUTERNAME") _
      &  "', '" & BACKUP_PATH & "', '" & strBackupFile  & "');"
    DoCmd.RunSQL  SQL
    SQL =  "DELETE * FROM BackupDetails WHERE BackupDate < date() - 30;"
    DoCmd.RunSQL  SQL
    DoCmd.SetWarnings  True
    BackupOnOpen_Exit:
    Exit  Function
    BackupOnOpen_Err:
    MsgBox  Err.Description, , "BackupOnOpen()"
    Resume  BackupOnOpen_Exit
    End  Function
                         
    ' This  function is given a filename complete with path, and
    ' a boolean value.
    ' If the  boolean is true, this function returns only the
    ' filename (without the path).
    ' If the  boolean is false, this function returns only the
    ' path (without the filename).
    '  Author:   Mike Perris - mikeperris.com
    '  Date:     13-Mar-2011
    '  Version:  1.1
    ' Revision  History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' 1.1       13/3/2011               Added the boolean bit and  extended code to
    '                                   extract path  as well as filename.
    Function  GetFileName(FullPath As String, IsFile As Boolean)
    Dim icount  As Integer
    icount =  Len(FullPath)
    Do Until  Mid(FullPath, icount, 1) = "\"
     icount = icount - 1
    Loop
    
    If IsFile  Then
     GetFileName = Right(FullPath, Len(FullPath)  - icount)
    Else
     GetFileName = Left(FullPath, icount)
    End If
    End  Function
    Can anyone tell me which part of the code to change to backup the backend file rather than the frontend? I assume it's the code in red but I'm a VBA noob so your help is appreciated.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    To continue with your endeavor I would change these two lines
    strSourcePath = GetFileName(CurrentDb.Name, False) ' false means we want pathname
    strSourceFile = GetFileName(CurrentDb.Name, True) ' true means we want filename


    to
    strSourcePath = "\\ServerName\FolderName\FileName.Extension"

    Where strSourcePath = the UNC path to your backend file

  3. #3
    Elfear is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    2
    Quote Originally Posted by ItsMe View Post
    To continue with your endeavor I would change these two lines
    strSourcePath = GetFileName(CurrentDb.Name, False) ' false means we want pathname
    strSourceFile = GetFileName(CurrentDb.Name, True) ' true means we want filename


    to
    strSourcePath = "\\ServerName\FolderName\FileName.Extension"

    Where strSourcePath = the UNC path to your backend file
    Success! Thanks for your help.

  4. #4
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    I am a newbie. I have use the above backup code to backup my access project while I was developing the code. All worked well. Now that I have split my project into a backend I am trying to backup the backend.

    Code:
    Option Compare Database
    Option Explicit
    ' This  function copies the Access database to a backup
    ' location when the database is  opened.
    ' It does  this only once in any given day.
    '  Author:   Mike Perris - mikeperris.com
    '  Date:     01-Jun-2012
    '  Version:  1.2
    
    
    Public Function BackupOnOpen()
    
    
    ' ***  CHANGE THE FOLLOWING LINE TO MATCH YOUR BACKUP DESTINATION
    ' Ensure you have a \ on the end of the pathname.
    Const BACKUP_PATH = "F:\NDC Documents\NDC Access\AutoBackup\"
    
    
    On Error GoTo BackupOnOpen_Err
    
    
    ' If DCount("BackupDate", "tblBackupDetails", "BackupDate = date()") <> 0 Then
     ' Exit Function
    ' End If
    
    
    Dim strSourcePath As String
    Dim strSourceFile As String
    Dim strBackupFile As String
    'strSourcePath = GetFileName(CurrentDb.Name, False)   '  false means we want pathname
    'strSourceFile = GetFileName(CurrentDb.Name, True)    '  true means we want filename
    
    
    
    
    strSourcePath = "F:\NDC Documents\NDC Access\"
    strSourceFile = "Beneficiaries_be.accbd"
    
    
    strBackupFile = "BackupDB-" & Format(Date, "yyyy-mm-dd") _
    & "_" & Format(Time, "hh_mm") & "-" & strSourceFile  ' This Works but time is wrong.
    
    
    ' strBackupFile = "BackupDB-" & Format(Date, "yyyy-mm-dd") _
    & "_" & Format(Now, "yyyy-mm-dd hhnnss") & "-" & strSourceFile
    
    
    ' Format(Now, "yyyy-mm-dd hh\:nn\:ss")
    MsgBox strSourcePath & strSourceFile 'Returns correct value
    MsgBox BACKUP_PATH & strBackupFile 'Returns correct value
    
    
    
    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject") 'File not found error
    fso.CopyFile strSourcePath & strSourceFile, BACKUP_PATH & strBackupFile, True
    Set fso = Nothing
    DoCmd.SetWarnings False
    Dim SQL As String
    SQL = "INSERT INTO tblBackupDetails " _
      & "(BackupDate, ComputerName, BackupFolder, Filename) " _
      & "VALUES ('" & Date & "', '" & Environ("COMPUTERNAME") _
      & "', '" & BACKUP_PATH & "', '" & strBackupFile & "');"
    DoCmd.RunSQL SQL
    SQL = "DELETE * FROM tblBackupDetails WHERE BackupDate < date() - 30;"
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    BackupOnOpen_Exit:
    Exit Function
    BackupOnOpen_Err:
    MsgBox Err.Description, , "BackupOnOpen()"
    Resume BackupOnOpen_Exit
    End Function
                         
    ' This  function is given a filename complete with path, and
    ' a boolean value.
    ' If the  boolean is true, this function returns only the
    ' filename (without the path).
    ' If the  boolean is false, this function returns only the
    ' path (without the filename).
    '  Author:   Mike Perris - mikeperris.com
    '  Date:     13-Mar-2011
    '  Version:  1.1
    ' Revision  History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' 1.1       13/3/2011               Added the boolean bit and  extended code to
    '                                   extract path  as well as filename.
    Function GetFileName(FullPath As String, IsFile As Boolean)
    Dim icount  As Integer
    icount = Len(FullPath)
    Do Until Mid(FullPath, icount, 1) = "\"
     icount = icount - 1
    Loop
    
    
    If IsFile Then
     GetFileName = Right(FullPath, Len(FullPath) - icount)
    Else
     GetFileName = Left(FullPath, icount)
    End If
    End Function
    At the fso line of code I get a File not found error.

    My access project in not on a network. It is on a single PC.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Frontend Backend Database Questions
    By data808 in forum Access
    Replies: 29
    Last Post: 03-26-2014, 11:58 PM
  2. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  3. Access Switching to frontend and backend
    By caliskier in forum Access
    Replies: 4
    Last Post: 11-19-2012, 11:58 AM
  4. Passwording frontend / backend Access 2010
    By erkwong in forum Security
    Replies: 0
    Last Post: 01-30-2012, 01:28 PM
  5. Replies: 1
    Last Post: 01-12-2012, 09:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums