autobackup database

[THREE05]

Hi guys.
How can I do to program my database to autobackup with a button or when exit ? I hate to go to file, save as, backup database. I would like something nice like automatic

please help :banghead:

 

[missinglinq]

I saw this somewhere, recently, and archived it. I haven't tried it myself, but the OP of the post reported that it worked for him without any problem.

Create a SubFolder to the Folder that holds your Access File, naming it LatestBackups. Next, enter this code into a Standard Module.

Public Function BackUpAccess()
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String

sourceFile = CurrentProject.FullName
path = CurrentProject.path
name = CurrentProject.name
destinationFile = path & "\LatestBackups\" & Left(name, Len(name) - 6) & "_backup" & "_" & _
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"

'this removes a file created on the same day
If Dir(destinationFile) <> "" Then
Kill destinationFile
End If

'this creates a backup into destination path
If Dir(destinationFile) = "" Then

Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile, True

MsgBox "A database backup has been stored under " & destinationFile
End If
End Function

Save the Module, naming it modBackUp.

In an appropriate event, enter

Call BackUpAccess()

Linq ;0)>

 

[THREE05]

Exelent my friend I definitely try this and I let you know... have a good day.

 

[bwc]

I saw this somewhere, recently, and archived it. I haven't tried it myself, but the OP of the post reported that it worked for him without any problem.

Create a SubFolder to the Folder that holds your Access File, naming it LatestBackups. Next, enter this code into a Standard Module.

Public Function BackUpAccess()
Dim sourceFile As String, destinationFile As String
Dim aFSO As Variant
Dim path As String, name As String

sourceFile = CurrentProject.FullName
path = CurrentProject.path
name = CurrentProject.name
destinationFile = path & "\LatestBackups\" & Left(name, Len(name) - 6) & "_backup" & "_" & _
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"

'this removes a file created on the same day
If Dir(destinationFile) <> "" Then
Kill destinationFile
End If

'this creates a backup into destination path
If Dir(destinationFile) = "" Then

Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile, True

MsgBox "A database backup has been stored under " & destinationFile
End If
End Function

Save the Module, naming it modBackUp.

In an appropriate event, enter

Call BackUpAccess()

Linq ;0)>

i added a "check for dir" to the code and the code will make the directory and copy the file if the dir does not exist, but i get a "runtime error 75: path/file access error" on the MkDir (destPath) line if the dir already exists. what am i missing?

Public Function BackUpAccess()

    Dim sourceFile As String, destinationFile As String
    Dim aFSO As Variant
    Dim sourcePath As String, sourceName As String
    Dim destPath As String, destName As String
    
    sourceFile = CurrentProject.FullName
    sourcePath = CurrentProject.path
    sourceName = CurrentProject.name
    destPath = sourcePath & "\backup"

    
    ' make backup directory if not exists
    If Dir(destPath) = "" Then
        MkDir (destPath)
    End If
    
    destinationFile = destPath & "\" & Left(sourceName, Len(sourceName) - 6) & "_backup" & "_" & Format(Now(), "yyyymmdd") & ".accdb"
    
    'this removes a file created on the same day
    If Dir(destinationFile) <> "" Then
        Kill destinationFile
    End If
    
    'this creates a backup into destination path
    If Dir(destinationFile) = "" Then
        
        Set aFSO = CreateObject("Scripting.FileSystemObject")
        aFSO.CopyFile sourceFile, destinationFile, True
        
        'MsgBox "A database backup has been stored under " & destinationFile
    End If
    
End Function

 

[theDBguy]

Hi Bryan,

Try to do a Debug.Print destPath, so you can see what folder path you're trying to create. If it's a full path, I am not sure MKDIR can do that, but I could be wrong though. Cheers!

PS. I just did a quick test. MKDIR cannot make a subfolder if the parent folder does not exist. You'll have to create one folder at a time, it seems.

 

[bwc]

Hi Bryan,

Try to do a Debug.Print destPath, so you can see what folder path you're trying to create. If it's a full path, I am not sure MKDIR can do that, but I could be wrong though. Cheers!

PS. I just did a quick test. MKDIR cannot make a subfolder if the parent folder does not exist. You'll have to create one folder at a time, it seems.

the directory does exist, i was expecting the code to skip the mkdir if it evaluated false

 

[theDBguy]

the directory does exist, i was expecting the code to skip the mkdir if it evaluated false

Again, did you do a debug.print to verify what the computer was seeing versus what your eyes are seeing? Just curious...

 

[bwc]

Again, did you do a debug.print to verify what the computer was seeing versus what your eyes are seeing? Just curious...

debug.print shows C:\Users\bryan\Documents\msAccess\beacon\backup; so why does the code not skip the mkdir?

 

[theDBguy]

debug.print shows C:\Users\bryan\Documents\msAccess\beacon\backup; so why does the code not skip the mkdir?

If that folder exists, then that's a good question. Can you change the debug.print to the following and post a screenshot of the result, please? Thanks.

MsgBox "[" & destPath & "]"

 

[bwc]

If that folder exists, then that's a good question. Can you change the debug.print to the following and post a screenshot of the result, please? Thanks.

MsgBox "[" & destPath & "]"

 

[bwc]

i am still useing access 2010, if that matters

 

[theDBguy]

View attachment 118861

Okay, it's starting to get interesting. Could you try the following now? Thanks.

MsgBox Dir(destPath)

After that, try it this way.

MsgBox Dir(destPath, 16)

 

[bwc]

View attachment 118861

 

[theDBguy]

Hi Bryan. Did you see my last post? Just curious...

 

[bwc]

Hi Bryan. Did you see my last post? Just curious...

 

[bwc]

Okay, it's starting to get interesting. Could you try the following now? Thanks.

MsgBox Dir(destPath)

After that, try it this way.

MsgBox Dir(destPath, 16)

MsgBox Dir(destPath) came up empty, whats that tell me?

 

[theDBguy]

MsgBox Dir(destPath) came up empty, whats that tell me?

That tells me that maybe you're not looking at a folder.

View attachment 118865

So, if the new code I provided returns that, then perhaps changing your code should make it work now?

If Dir(detPath, 16) = "" Then

 

[bwc]

That tells me that maybe you're not looking at a folder.

So, if the new code I provided returns that, then perhaps changing your code should make it work now?

If Dir(detPath, 16) = "" Then

that did it! thanks for your help

 

[theDBguy]

that did it! thanks for your help

Glad to hear you got it sorted out. Good luck with your project.