Backup database using Macro on Scheduled Task 1

[aarondewberry]

All

I run a macro on a scheduled task every evening. What I want to do is to get the scheduled task to take a backup of my database and store it in a folder called "backup" on my C drive, with the date that the backup was created at the end.
eg. C:\backup\mydatabase - 240407.mdb

This backup is to be taken after my macro has run. Is there a command i can put at the end of the macro? Or would i have to create a different scheduled task using another macro?

 

[Remou]

You can use the to backup open files. For example:

'Ref: Microsoft Scripting Runtime
Dim fs As Scripting.FileSystemObject
Dim strBackUpFile As String

strBackUpFile = "C:\backup\" & Format(Date, "yymmdd") & ".mdb"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile CurrentProject.FullName, strBackUpFile

 

[aarondewberry]

Hi Remou

Excuse my ignorance (lack of knowledge). Where do i paste this code?

Thanks

 

[Remou]

Back-up your database before doing anything. Add a module and create a sub or function. Add the code to the sub or function. If you intend to use macros to run this, you are best off with a function:

Function TestBU()
'Ref: Microsoft Scripting Runtime
Dim fs As Object 'Scripting.FileSystemObject
Dim strBackUpFile As String

strBackUpFile = "C:\backup\" & Format(Date, "yymmdd") & ".mdb"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile CurrentProject.FullName, strBackUpFile
End Function

I have changed the above slightly so that you do not need to reference the Scripting Runtime, however, you will need to test and to add error coding.

 

[aarondewberry]

Thanks Remou

When I'm running the code it keeps coming up with a Run Time Error '76' - path not found - on the final part for the code.
fs.CopyFile CurrentProject.FullName, strBackUpFile

 

[Remou]

You need to set:
[tt]strBackUpFile = "C:\backup\" & Format(Date, "yymmdd") & ".mdb"[/tt]

To the name of the path you wish to use, "C:\backup\" is an example.

 

[aarondewberry]

So simple Remou.

Many thanks for your help