Automated Backup of Database Backs up Linked Tables

[tasawer]

Hi,

with the code below, I was successfully backing up my database regularly until I separated the Front end and used linked tables. Now only the linked tables are being backed up. (Not much good)!

how can I reference my BackEnd Tables Database 'acc.mdb' and create an automated copy.

Private Sub BackupData()
    
    Dim sFile As String
    Dim objectDB As DAO.Database
    Dim oTbl As TableDef
  
    sFile = "s:\acc_" & Format(Date, "ddmmyyyy") & ".mdb"
    If Dir(sFile) <> "" Then Kill sFile

    Set objectDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
    objectDB.Close
    
    DoCmd.Hourglass True
    For Each oTbl In CurrentDb.TableDefs
        If Left(oTbl.Name, 4) <> "msys" Then
        DoCmd.CopyObject sFile, , acTable, oTbl.Name
        End If
        'DoCmd.TransferDatabase acExport, "Microsoft Access", sFile, acTable, oTD.Name
    Next oTbl
    DoCmd.Hourglass False

End Sub

I have tried adding the following code to make the BE_Tables database as CurrentDatabase , but it did not work.

    
...
    Dim oDB2 As dao.Database
    Dim sData As String
    sData = "s:\acc786_data.mdb"
    Set oDB2 = DBEngine.Workspaces(0).OpenDatabase(sData)

    DoCmd.Hourglass True
    For Each oTbl In oDB2.TableDefs
...

Help is greatly appreciated.

[NeoPa]

I'm not sure if there's a more straightforward way but certainly Application Automation would enable you to do what you need with the BE.

I'm fairly sure that Copy/Paste now gives the option to paste as a local table nowadays though. Have you looked into that?

[twinnyfo]

tasawer,

I'm not sure if this is along the line of what you are interested in.

In my database, I use a splash form that automatically backs up my file(s), dates them, and deletes any files older than 30 days. This has worked reliably for me for several years without a single fail.

I put this into my splash form in the OnTimer Event (initial Timer Interval set to 1):

Private Sub Form_Timer()
On Error GoTo EH
    Dim strDBASEDATA as String
    Dim strBACKUPPATH as String
    Dim strDate As String
    Dim strFile1 As String
    Dim strFile2 As String
    Dim FSO
    Dim strSQL as String
    Dim strFileName As String

    Me.txtLastBackup = DLookup("[LastBackup]", "tblBackupDate")

    'Make Backup of Database
    If Me.txtLastBackup < Now - 0.25 Then
        'This will back up the file every time someone logs into the DB
        'As long as it has been 6 hours since the last log in
        strDBASEDATA = "\\DatabasePath\"
        strBACKUPPATH = "\\BackupPathpath\"
        strDate = Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hh-mm")
        strFile1 = strDBASEDATA & "DatbaseName.accdb"
        strFile2 = strBACKUPPATH & DatabaseName BACKUP - " & _
            strDate & ".accdb"
        Set FSO = CreateObject("Scripting.FileSystemObject")
        FSO.CopyFile strFile1, strFile2
        Me.txtLastBackup = Now
        strSQL = "UPDATE tblBackupDate SET" & _
            " tblBackupDate.LastBackup = #" & Now & "#;"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If
    'Delete old Backup Files
    strFileName = Dir(strBACKUPPATH)
    Do While strFileName <> ""
        If Date - 30 > CDate(Mid(strFileName, Len(strFileName) - 21, 10)) Then
            Kill strBACKUPPATH & strFileName
        End If
        strFileName = Dir()
    Loop
    TimerInterval = 0
    Exit Sub
EH:
    If Err.Number = 13 Then
        'My Backup path should have nothing but backup files in it
        'However, if there is a stray file without the standard
        'naming convention just move along to the next file
        Resume Next
    Else
        MsgBox "There was an error backing up Database!  " & _
            "Please contact your Database Administrator.", vbCritical, "Error!"
        Resume Next
    End If
End Sub

I also have a Table, with one record and one field, which houses the last backup date. Then, there is a text box on the form that displays that date/time for the user.

I hope this helps you along your way!

[tasawer]

Thank You Twinnyfo. This has worked a treat.

[twinnyfo]

Wonderful! I'm glad I could help out! Let us know if you have any other questions.

[bbennett902]

When you set the variables of your backup path. Make sure it's not in the If statement to check when the last backup was. Otherwise it doesn't get set correctly when you go through the delete backups loop if the last backup was less than 6 hours ago and it will delete files from the default directory opened.