Setting a backup button (1 Viewer)

[Mat1994]

Hi All,

I need help setting a backup button.
I've found the thread :
https://access-programmers.co.uk/forums/showthread.php?t=199405

Working step by step, I created a from (frm_Backup). I have a problem with the code of the backup button :

Private Sub Button_Backup_Click()
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo Backup_Button_Backup
'buf = Back Up Folder
'buf is created if it does not exist
'CurrentProject.Path = the path that the FE is located
buf = CurrentProject.Path & "\Backups\"
MkDir buf
    Resume Backup_Button_Backup
Backup_Button_Backup:
'Use yyyy-mm-dd hh-mm-ss as folder name. Change as needed.
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
str = CurrentProject.Path & "\Backups\" & MD_Date
'Source = where the data is stored
source = CurrentProject.Path & "\Data\"
MkDir str
Set fs = CreateObject("Scripting.FileSystemObject")
'Change the file extension as needed
fs.CopyFile source & "*.accdb", str
Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successfully!", _
        vbInformation, "Backup Successful"
Exit_Button_Backup:
  Exit Sub
Err_Button_Backup:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox Err.Description, vbExclamation, "Error Creating " & str
  
End If
    Resume Exit_Button_Backup

End Sub

After reading the thread I linked, I created a folder "Data" and placed my database in it. When I test the button and click on the backup button I get the following error :
Run-time error '76' : Path not found

When I debug the code the following line is underlined in yellow :

fs.CopyFile source & "*.accdb", str

In addition to the error message I get, the folder "Backups" is still created but remains empty.
I've attached a sample of the backup I'e done.

Thank you for your help,
Mat

 

[pbaldy]

If the db is in the folder named data, then this is adding a second folder named data, which doesn't exist.

source = CurrentProject.Path & "\Data"

In the immediate window, see what this returns:

?currentproject.Path

 

[Mat1994]

Hi,

Ok, so I placed my db in another folder to avoid conflict with the name "Data".

I'm not sure I understand you. Where do you want me to write :

?currentproject.Path

Mat

 

[pbaldy]

In the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

that will tell you what that is returning for you.

 

[Mat1994]

Thank you.

When I ?currentproject.Path in the immediate window I get the following message :
I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\03-New Access Database

 

[Mat1994]

Hi All,

So I've tried a few things but none seem to work.

I've changed :

Before : source = CurrentProject.Path & "\Data"
After : source = CurrentProject.Path & "\I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\03-New Access Database\02-Export_WIP"

Before : fs.CopyFile source & "*.accdb", str
Test 1 After : fs.CopyFile source & "\.accdb", str
Test 2 After : fs.CopyFile source & "\.mdb", str
Test 3 After : fs.CopyFile source & "*.mdb", str

After each test I still get the error message : Run-time error '76' : path not found. And the line fs.CopyFile source & "*.accdb", str is alway underlined in yellow.
After each test, the "backups" folder is created, in it, the folder named Date+time is created but it remains empty.

Does anyone have a suggestion?

Thank ou for your help,
Mat

 

[theDBguy]

Hi. May be too late to the party but how about trying this function out? Just a thought...

 

[Mat1994]

I've also tried this after finding this thread : https://www.access-programmers.co.uk/forums/showthread.php?t=204908

So I changed :
Before : buf = CurrentProject.Path & "\Backups"
After : buf = "\I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\03-New Access Database\02-Export_WIP\Backups"

And
Before : source = CurrentProject.Path & "\Data"
After : source = CurrentProject.Path

But it still not working.

Mat

 

[pbaldy]

Whatever your line is, for example:

fs.CopyFile source & "*.accdb", str

right before that put

Debug.Print source & "*.accdb"

and see what it's resolving to. I think you'll see it's not a valid path. The key is to adjust your variables until they resolve correctly.

 

[Gasman]

OK, I took your code and put into a test db of mine.

Firstly do you have a Data folder in the current project path.?
Secondly if you do, do you have files of the correct type in that folder.?

 

[Mat1994]

Thanks,

TheDBguy,
Thank, I'll try yours if I can't get mine to work.

Pbaldy,
So I wrote Debug.Print source & "*.accdb" just before fs.CopyFile source & "*.accdb", str
And I get in the immediate window the following message :
I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\03-New Access Database\02-Export_WIP\Data\*.accdb


Gasman,

Firstly do you have a Data folder in the current project path.?

I didn't. Just tried again with a folder named Data in the same folder as the db. I also tried putting the db in the data folder.
Still doesn't work

 

[Gasman]

Well I don't know what to tell you.
Once I created the Data folder, it still did not work as there was no file found message.
I placed a db in there (not the one runing) , and the copy worked and completed message displayed.

As you have it now the DB with the code should be in the parent folder of Data. Not really sure that is what you want.?

 

[Mat1994]

As you have it now the DB with the code should be in the parent folder of Data. Not really sure that is what you want.?

I thougth the code would create a new folder "backups" to store the backup. Idealy, that's what I want because I won't have to think about where the backups are stored if my db changes folder.

So you did was :
Create a folder with the db + a folder Data
Placed a db (other then the running one) in the data folder
and it worked?

 

[Gasman]

It does create the backups folder. That folder is created in the currentproject.path

However you are looking in a Data folder under currentproject.path for any files.
At first I did not have the data folder, so path not found. So I created it.
Then it said file not found as there were no files in that Data folder.?
So I just copies another DB to the data folder and then it worked.

I think you should be copying from the currentproject.path
I just did that and all my DBs including the one I was running the code from were copied?

 

[Mat1994]

Ok, so I created a folder Data in the same folde as the db. I placed a db in it.
Then the backup button worked.

Would it be possible to improve the code so it just create a backup folder in the same folder as the db?
I would like to avoid having to create the Data folder and make sure there is a a db in it.

 

[Gasman]

Try this

Private Sub Button_Backup_Click()
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo Backup_Button_Backup
'buf = Back Up Folder
'buf is created if it does not exist
'CurrentProject.Path = the path that the FE is located
buf = CurrentProject.Path & "\Backups\"
MkDir buf
    Resume Backup_Button_Backup
Backup_Button_Backup:
'Use yyyy-mm-dd hh-mm-ss as folder name. Change as needed.
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
str = CurrentProject.Path & "\Backups\" & MD_Date
'Source = where the data is stored
source = CurrentProject.Path & "\"
MkDir str
Set fs = CreateObject("Scripting.FileSystemObject")
'Change the file extension as needed
fs.CopyFile source & "*.accdb", str
Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successfully!", _
        vbInformation, "Backup Successful"
Exit_Button_Backup:
  Exit Sub
Err_Button_Backup:
  If Err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox Err.Description, vbExclamation, "Error Creating " & str
  
End If
    Resume Exit_Button_Backup

End Sub

 

[Mat1994]

Thank your code works perfect.

I've noticed another problem.
I've noticed that the backup button doesn't create a copy of the db I'm have open. Instead it creates a copy of a db located in another folder (Documents) of my local network.
Do you know why?

 

[Gasman]

All I did to the code was remove the \data\ from the source path, so now it will copy all accdb files in your currentproject.path

I was surprised that it even copied the DB I was running the code from, but it did.

I cannot see how it can copy from a path other than the path of the db you have the code in, unless you have changed the code.?

 

[Mat1994]

I cannot see how it can copy from a path other than the path of the db you have the code in, unless you have changed the code.?

No, my db is located here whitch is a network all my collegues have acces to :
I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\03-New Access Database\02-Export_WIP

But it copies the db located here in Documents that is in my local network.

 

[Mat1994]

Sorry about that. Got a collegues to have a look at the code and indeed this line (source = CurrentProject.Path & "") was put as a comment.

Now the code works perfect! Thank you for you help.
Mat