Backup Template - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Backup Template

Tony Roberts

5 years ago

Subject: BackUp
Comments: What is wrong here? I have copied and checked and checked but get an error on O.CopyFile SourceFile, DestFile

Any help is appreciated!

'BACKUP ROUTINE
Private Sub doBackup()

Dim SourceFolder As String, SourceFile As String, DestFolder As String, DestFile As String Dim O As Object

SourceFolder = CurrentProject.Path
DestFolder = "Z:\AccessBackup"

On Error Resume Next
MkDir DestFolder
On Error GoTo 0

SourceFile = CurrentProject.Name
DestFile = SourceFile & ".BACKUP." & Format(Now(), "yyyymmdd-hhnnss") & ".accdb"

SourceFile = SourceFolder & "\" & SourceFile DestFile = DestFolder & "\" & DestFile

Set O = CreateObject("Scripting.FileSystemObject")
Status "Copying " & SourceFile & " to " & DestFile O.CopyFile SourceFile, DestFile Set O = Nothing

Status "Done"

End Sub

	Kevin Robertson @Reply 5 years ago
Looks like Set O = Nothing should be on a separate line. If that isn't the problem, what error message are you getting?

	Tony Roberts OP @Reply 5 years ago
Thanks SET O = Nothing is in the code Run-time error 70 permission denied - is the error message, but there is full access to the storage drive. Many thanks, tony.

	Tony Roberts OP @Reply 5 years ago
When I get the error this line in the code highlights O.CopyFile SourceFile, DestFile

	Kevin Robertson @Reply 5 years ago
Here is a page I found on that specific error. https://www.techinpost.com/how-fix-solve-runtime-error-70-code-problem/

	Adam Schwanz @Reply 5 years ago
Like Kevin said, that Set O = Nothing is not included in that line, it's on its own line right? What happens if you just wipe out that whole line? It's only a status update. Status "Copying " & SourceFile & " to " & DestFile O.CopyFile SourceFile, DestFile Set O = Nothing just make sure you keep the Set O = Nothing

	Richard Rost @Reply 5 years ago
MsgBox your final SourceFile and DestFile variables. Maybe you've got an invalid character in there.

	Tony Roberts OP @Reply 5 years ago
Thanks everyone, tried all of this but still have same error. if I take out the SET O = ... line everything works fine except it does not copy the file! I think it is a permission issue on the machine.

	Richard Rost @Reply 5 years ago
On the contrary, I'll bet NOTHING is working because you've got "On error resume next" in there. You won't see any other error messages. Msgbox your SourceFile and DestFile variables. Let me see a screen shot.

	Richard Rost @Reply 5 years ago
Get rid of the error handler and you'll see what line is causing the problem.

	Tony Roberts OP @Reply 5 years ago
Many thanks Richard, I have emailed the information and attachments as requested. Really appreciate everyone's help!

Scott Axton @Reply

5 years ago

Tony -

FYI - Richard probably wont open your file. There are to many issues,  besides time , to open all of the students files.
He didn't ask you to send him your files he asked you to screenshot your code and post it here.
You can do that using the upload images link in the top right corner of your original post.

So like this:
SourceFile = CurrentProject.Name
DestFile = SourceFile & ".BACKUP." & Format(Now(), "yyyymmdd-hhnnss") & ".accdb"
Msgbox SourceFile
Msgbox DestFile

For now:  Comment out the lines On Error Resume Next and On Error GoTo 0 by placing an apostrophe at the first of the line.  Like this: 'On Error Resume Next   and   'On Error GoTo 0

In the comments above the guys are telling you that the  Set O = Nothing  should be on it's own line.

	Scott Axton @Reply 5 years ago
To test if you are having permission issues writing to your server try creating a folder on your desktop. Make that folder path your DestFile. I created a folder on my desktop of AccessBackup so for me the path would be: "C:\Users\sgaxt\Desktop\AccessBackup" Yours will be different. Right click the folder and look at properties to see your path.

	Scott Axton @Reply 5 years ago
Just noticed another issue. I'm assuming you copied and pasted your code here. In you dim statement at the top - ..., DestFile As String Dim O As Object is wrong. It should be like this: ..., DestFile As String, O As Object Or like this with Dim for the object on a new line: ..., DestFile As String Dim O As Object

	Richard Rost @Reply 5 years ago
Yeah Tony emailed me his file and I explained to him that I don't open attachments. He did send me a screen shot of his filenames though...

	Richard Rost @Reply 5 years ago

	Richard Rost @Reply 5 years ago
I think the problem MAY be the spaces in your path/filenames. You might have to put quotes around them. `SourceFile = """" & SourceFile & """" DestFile = """" & DestFile & """"` That will produce a filename like this: `"C:\Users\My Path\Some File.txt"` Try that.

	Tony Roberts OP @Reply 5 years ago

	Tony Roberts OP @Reply 5 years ago

	Tony Roberts OP @Reply 5 years ago
Thanks everyone, really appreciate this. New to the Forum, so getting to know how it works. Source and destination files MsgBox uploaded, also code to follow so that it keeps its formatting. I have changed the DestFolder location to C:\SchoolMIS\Backup to avoid naming issues but it is still failing at the highlighted line?

	Tony Roberts OP @Reply 5 years ago

	Richard Rost @Reply 5 years ago
I want to see the value of what's in SourceFile and DestFile. MSGBOX them and post a screen shot of that.

	Richard Rost @Reply 5 years ago
Also what is the EXACT error message you're getting?

	Tony Roberts OP @Reply 5 years ago
Thanks Richard, attachments coming.

	Tony Roberts OP @Reply 5 years ago

	Tony Roberts OP @Reply 5 years ago

	Tony Roberts OP @Reply 5 years ago

	Richard Rost @Reply 5 years ago
Well, all I can say is to make sure those are valid paths and filenames. I can't see your computer from here, so I can't check that. Also try the quotes thing I mentioned above.

	Scott Axton @Reply 5 years ago
Maybe I'm blind but I don't see a & "\" & behind the DestFolder and DestFile. It might be under the tooltip in the screen shot.

	Tony Roberts OP @Reply 5 years ago
Many thanks to everyone for all your comments and support, very much appreciated. I eventually worked it out and all is fine now. The problem was in the lines: O.CopyFile SourceFile, DestFile CopyFile SourceFile, DestFile You don't need both of these, I removed CopyFile SourceFiles, DestFile and it worked perfectly! Now to solve the next problem!

	Richard Rost @Reply 5 years ago
That makes perfect sense. You're right - you only need to issue that call ONCE, and you NEED the "O" there. It was very hard to tell from your initial post because you merged multiple commands together on one line.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View