automatic backup access db w/new file name

[bobalou]

I want to schedule an automatic backup of several access databases from a server to my pc because of problems we are having w/the network backup.

I want to copy over to local drive w/a new name (original name w/date appended to end) and use the scheduler to run overnight.

bat file, vb, vb script, whatever will work.

Anyone have any ideas?

thanks

 

[Remou]

VBScript and the FileSystemObject can be used with the Windows scheduler.

http://lessthandot.com

 

[bobalou]

I kinda figured that but what I am looking for is the script to do it.

I was trying to do it using a bat file. I can get it to do the copy part just fine but I want to rename the file so that I do not copy over the previous backup. Somehow the db is getting corrupted ocassionally and I want to be able to roll back to the previous day. We lost a weeks worth of data the last time (a lot of the IT around here has been outsourced). The rename part it what I am having a problem with.

 

[Remou]

Not .bat, .vbs

strFile = "C:\Docs\File.mdb"
strNew = Mid(strFile, 1, InStrRev(strFile, ".")) & "bak"
Set fs = CreateObject("scripting.filesystemobject")
fs.CopyFile strFile, strNew, False

http://lessthandot.com

 

[bobalou]

Thanks, but won't that rename the file the same thing every time rather than one with a unique name? Unless I am misunderstanding the functioning of the third line above.

 

[Remou]

Dear me.

Dim fs As FileSystemObject
strFile = "db1.mdb"
strPath = "C:\Docs\"

Set fs = CreateObject("Scripting.FileSystemObject")

strNew = strPath & strFile & Year(Date) _
    & Month(Date) & Day(Date) & ".bak"
    
a = fs.FileExists(strNew)

i = 1

Do While a
    strNew = strPath & strFile & Year(Date) _
        & Month(Date) & Day(Date) & "_" & I & ".bak"
        
    a = fs.FileExists(strNew)
    i = i + 1
    If i > 9 Then
        a = True
        MsgBox "Too many attempts"
        WScript.Quit
    End If
Loop

fs.CopyFile strPath & strFile, strNew, False

http://lessthandot.com

 

[bobalou]

I just got time to try what you sent. I am getting an error. Error is Line: 1, Character: 8, Expected end of statement, Code: 800A0401 I don't see anything wrong w/the letter A in line 1.

 

[PHV]

Get rid of " As FileSystemObject

 

[bobalou]

Thanks, now it is happy!