Create Automated Backup of Database By Month

[JithuAccess]

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

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.

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

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

To:

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

 

[The_Doc_Man]

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:

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

 

[JithuAccess]

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:

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

Thank You so much

 

[The_Doc_Man]

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.

 

[Pat Hartman]

Take a look at the utilities for sale at FMSINC.com

 

[JithuAccess]

Take a look at the utilities for sale at FMSINC.com

Thank You

 

[Pat Hartman]

You're welcome.