Permission Denied while trying to save a back-up copy through VBA code

[SachAccess]

Hi,
I am using below code to create back-up of my MS Access file.
I have changed the original names however, have checked the actual path name manually and it is accessible.
This code was written yesterday. It was working fine till one our ago. In fact I have created multiple back-ups files with this code.

I tried to change this code to function and get it run via AutoExec, it did not work for me due to some reasons.
Hence I reverted from Function to Sub. And thought that I will assign a VBA Sub to a button for this.
However, when am running the same code now, am getting an error at FileCopy SourceFile, DestinationFile saying permission denied.
I am not able to understand the reason. I have not changed anything in the code. It was working fine. Then why this error.
Can anyone please help me in this.

Sub BackUp()
Dim SourceFile, DestinationFile
Dim MyFileName As Variant
SourceFile = "M:\ MyActualPathHere \MyActualFileName.accdb" ' Define source file name.
MyFileName = Day(Now)
MyFileName = MyFileName & "-" & Month(Now)
MyFileName = MyFileName & "-" & Year(Now)
MyFileName = MyFileName & "-" & Hour(Now)
MyFileName = MyFileName & "-" & Minute(Now)
MyFileName = MyFileName & "-" & Second(Now)
MyFileName = "BackUpFile" & "-" & MyFileName & "-" & Trim(UCase(Environ("UserName"))) & ".accdb"
DestinationFile = "M:\MyActualPathHere\ " & MyFileName
FileCopy SourceFile, DestinationFile ' Copy source to target.
End Sub

 

[Ranman256]

i've had problems with FileCopy so use this instead:

DestFile = "m:\MyFileName.accdb_" & Format(Now(),"yymmdd-hhnn") & ".accdb"
Copy1File SrcFile, DestFile

Public Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim fso
On Error GoTo errMake

Set fso = CreateObject("Scripting.FileSystemObject")    '(reference: ms Scripting Runtime)
fso.CopyFile pvSrc, pvTarg
Copy1File = True
Set fso = Nothing
Exit Function

errMake:
'MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Function

 

[SachAccess]

i've had problems with FileCopy so use this instead:

DestFile = "m:\MyFileName.accdb_" & Format(Now(),"yymmdd-hhnn") & ".accdb"
Copy1File SrcFile, DestFile

Public Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim fso
On Error GoTo errMake

Set fso = CreateObject("Scripting.FileSystemObject")    '(reference: ms Scripting Runtime)
fso.CopyFile pvSrc, pvTarg
Copy1File = True
Set fso = Nothing
Exit Function

errMake:
'MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Function

Thanks a lot @Ranman256 , am checking this and will revert. Have a nice day ahead.

 

[SachAccess]

Hi @Ranman256 it is working perfect! Thanks.

 

[The_Doc_Man]

Just as a thought, and there is nothing wrong with what you are doing, but...

MyFileName = Day(Now)
MyFileName = MyFileName & "-" & Month(Now)
MyFileName = MyFileName & "-" & Year(Now)
MyFileName = MyFileName & "-" & Hour(Now)
MyFileName = MyFileName & "-" & Minute(Now)
MyFileName = MyFileName & "-" & Second(Now)

could be done as this:

MyFileName = Format( Now, "yyyy-mm-dd-hh-nn-ss" )

in which case the names would then sort in date order AND it would only take one line of code to achieve that string you are assembling. By putting the dates as year, month-number, day-number, you get automated calendar sorting within the folder.

Again, you are doing nothing wrong, but what you are doing is not as efficient as it could be.

 

[SachAccess]

Hi @The_Doc_Man thanks a lot for the help. I agree, mine is not efficient method.
I was trying with something similar to 'MyFileName = Format( Now, "yyyy-mm-dd-hh-nn-ss" )' only however I faced bug in my code.
So at that moment I thought to skip that part for the time being and try check if back-up is getting created.

Have a nice weekend.