Automatic backup.

[WithoutPause]

https://www.youtube.com/watch?v=Tkh8JOZ3Ah8



I used that as the source. Here's the issues.

1. If possible, I would like the backup to happen when a user exits the application.
2. I would like the backup to be overwritten if done in the same day.
3. Unlike in the video, I get a number of Microsoft Access Security Notices when running the code.

Current code.

Sub BackUp()
     
    Dim sFile As String, oDB As DAO.Database
     
    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".accdb"
    If Dir(sFile) <> "" Then Kill sFile
     
    Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
    oDB.Close
     
    Dim oTD As TableDef
     
    For Each oTD In CurrentDb.TableDefs
        If Left(oTD.Name, 4) <> "MSys" Then
            DoCmd.CopyObject sFile, , acTable, oTD.Name
        End If
    Next oTD


    ' No current queries
    ' Dim oQD As QueryDefs
    'For Each oQD In CurrentDb.QueryDefs
    '    If Left(oDQ.Name, 1) <> "~" Then
    '        DoCmd.CopyObject sFile, , acQuery, oQD.Name
    '    End If
    'Next oQD
    
    Dim oForm As Object
    For Each oForm In CurrentProject.AllForms
        DoCmd.CopyObject sFile, , acForm, oForm.Name
    Next oForm
    
    Dim oReport As Object
    For Each oReport In CurrentProject.AllReports
        DoCmd.CopyObject sFile, , acReport, oReport.Name
    Next oReport
    
    Dim oMod As Object
    For Each oMod In CurrentProject.AllReports
        DoCmd.CopyObject sFile, , acModule, oMod.Name
    Next oMod
    
    Dim oMac As Object
    For Each oMac In CurrentProject.AllMacros
        DoCmd.CopyObject sFile, , acReport, oMac.Name
    Next oMac
     
End Sub


Function AutoRun()
    Dim sFile As String
    sFile = "C:\Users\(username)\ACEDAO.dll"
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile sFile
    BackUp
End Function

[ItsMe]

Why not create a copy of the entire file? It seems you are importing all objects anyway.

[WithoutPause]

If there is an easier way, then I'm interested. I Googled the solution and most solutions were simply Save & Publish. I'm trying to make the backing up as dummy proof as possible.

[ItsMe]

Here is some code that will copy and paste a test file from your C drive. This is a re-factored version of the OP's version. It may still benefit from some additional massaging but it gets the job done.

Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
'Dim path As String, name As String
sourceFile = "C:\Test\CopyMe.txt"
'path = CurrentProject.path
'name = CurrentProject.name
destinationFile = "C:\Test\Result\CopyMe_Copy.txt"
'this detects if the file name already exists
If Dir(destinationFile) <> "" Then
MsgBox "This file name already exists! Choose another destinationFile or delete the existing destinationFile first."
End If
'this creates a backup into destination path
If Dir(destinationFile) = "" Then
Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile 'You can choose to overwrite existing file by including...   , True
MsgBox "A copy of your test file has been stored under " & destinationFile
End If