Create a Macro to run backup of database

[Harley Guy]

Hello all. I do a manual back-up of my databases each week by going into "File" then "Save & Publish" and click on "Back-up Database" and it will place the back-up in a location of my choosing. I would love to create a macro that I could place on the switchbaord so I could do this with one click.

I am unfamiliar with creating macros...can someone give me an idea on how to go about this?

Thanks!

[ItsMe]

I use task scheduler to launch a VB script file.

Here is the code for the VBS

Dim sourceFile
Dim destinationFile
Dim strDesFilName
Dim strNow
Dim aFSO
strNow = Year(Date) & "_" & Month(Date) & "_" & Day(Date) & "_" & Hour(Now) & Minute(Now) & "_" & Second(Now)
strDesFilName = "Inv_Copy_" & strNow & ".accdb"
destinationFile = "C:\Inventory_Backup\" & strDesFilName
sourceFile = "\\ServerName\Inventory\Inventory.accdb"
Set aFSO = CreateObject("Scripting.FileSystemObject")
aFSO.CopyFile sourceFile, destinationFile

[Harley Guy]

So this is a code that would schedule back-ups and I wouldn't need to do it manually? It's a little out of my league but I like the idea if that's the case.

[ItsMe]

The code would not schedule the back up. The code will create a backup and use the current system time as part of the file name. In order to schedule the backup you would have to use Task Scheduler in Windows. The hardest part is probably getting all of the settings in Task Scheduler correct.

You can start with the code here and test it with a dummy file in a couple of temp folders.

Identify the text in my code for the file paths and adjust that part of the code to match your new temp folders/file names. Be sure to include file extensions in the names of your files.

For instance, the following line of code would need to be edited so it will copy the file you want to back up. Right now, it is looking for a server named "ServerName" and a folder named "Inventory" where there is an accdb file named "Inventory"
sourceFile = \\ServerName\Inventory\Inventory.accdb

[Harley Guy]

Ah well...it was a shot in the dark. Since it is only once per week, I will continue to do it manually. This would be a little bit out of my league. Thanks anyway!

[ItsMe]

It aint that bad. Navigate to your DB file using windows explorer and set you cursor in the Address field at the top of the window. You will see some text highlighted. This will be your path. Maybe something like

C:\Test

This is using the drive letter for a local drive on the machine. The other example I provided is a path on a network.

[killdozer]

If I want to tell the user that the database was succeffully backed up, how would I do that?

[June7]

Use a MsgBox call in the script.