Create Automated Backup of Database By Month (1 Viewer)

JithuAccess

Member
Local time
Today, 13:10
Joined
Mar 3, 2020
Messages
297
Hello,

Is there any way to create a folder by current month and take Backup of the Database on that folder?

In a button click I want to create a folder for example 2022 Feb and then create a Backup of my Database in that folder like from 01-Feb-2022 to 28-Feb-2022.

On next month I want to create a folder 2022 Mar and then create Backup of my database there.

This is my current code

Code:
Dim Source As String
Dim Target As String
Dim retval As Integer

Source = CurrentDb.Name

Target = "M:\Database Backup\2022\Feb 2022\"
Target = Target & Format(Date, "dd-mmm-yyyy") & ".accdb"

retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(Source, Target, True)
Set objFSO = Nothing


If MsgBox("Are you sure you want to exit the Application?", vbYesNo + vbQuestion) = vbYes Then

DoCmd.Quit


Else

Exit Sub

End If

And this is how my Backups are saved in the folder.

1644878618194.png




This is working perfect but each month I have to create a New folder by Month and change the code from:

Code:
Target = "M:\Innovation & Policy\Claims & SPU Shared Database\Database Backup\2022\Feb 2022\"

To:

Code:
Target = "M:\Innovation & Policy\Claims & SPU Shared Database\Database Backup\2022\Mar 2022\"

So just wondering whether I can do this more automate

Thank you so much
 

Attachments

  • 1644878557873.png
    1644878557873.png
    39.3 KB · Views: 169

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:10
Joined
Feb 28, 2001
Messages
26,999
You should look up the File System Object (also written as FileSystemObject and sometimes as FSO), which allows you to manipulate files and directories including creating folders, renaming files, etc. You need the Windows Scripting library to get the FSO available to VBA.

It would be fairly easy to tell FSO to create a folder with a specific name, then use FSO's ability to make file copies in any folder you wish, with the files having any name you wish.

This link will get you to the top of a pyramid of topics that you can browse to see what FSO can do:

 

JithuAccess

Member
Local time
Today, 13:10
Joined
Mar 3, 2020
Messages
297
You should look up the File System Object (also written as FileSystemObject and sometimes as FSO), which allows you to manipulate files and directories including creating folders, renaming files, etc. You need the Windows Scripting library to get the FSO available to VBA.

It would be fairly easy to tell FSO to create a folder with a specific name, then use FSO's ability to make file copies in any folder you wish, with the files having any name you wish.

This link will get you to the top of a pyramid of topics that you can browse to see what FSO can do:

Thank You so much
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:10
Joined
Feb 28, 2001
Messages
26,999
As a side note, I have a minor issue with having a database back itself up. I usually have either a batch script or a stand-alone Access app that has no links to the target Back End file because you really want the BE file to be closed (i.e. no users in it) if you want to get a clean copy.
 

Users who are viewing this thread

Top Bottom