Automated backup

[ROMADOZ]

I am not a programmer... I need my DB to backup on close automatically. Help?

-ROMADOZ

 

[RuralGuy]

I believe ghudson has some advice in this link.

 

[ROMADOZ]

That was an excellent link... I think it will solve most of my issue. I just need some help on how to remove the whole winzip part, I do not need it zipped.

Here is what I have for my on click on my exit button. Can I put it in here somehow? I got a bit of help getting to this point, but my help can't get me any farther. As of right now, the DB repairs and compacts on exit... I need to not interfere with that when it is backing up.

Private Sub btn_Exit_Click()
On Error GoTo Err_btn_Exit_Click

If Me.Dirty Then Me.Dirty = False
DoCmd.Quit
Exit_btn_Exit_Click:
Exit Sub
Err_btn_Exit_Click:
MsgBox Err.Description
Resume Exit_btn_Exit_Click

End Sub


More help? Thanks much. Still pretty clueless.

 

[RuralGuy]

It seems to me the OP on the thread I pointed to also did not want it zipped and ghudson answered that question. Am I wrong here?

 

[ROMADOZ]

I believe it does... the page didn't fully load so I didn't see the whole thread. Now on to, where do I put it? Does it get dropped in somewhere in the button on click? Remember, I know nothing about VBA. I must be driving you nuts.

 

[RuralGuy]

The function goes in a standard module with a name that is different than the function. Since it does not return a value it could really be a Sub but it will work either way. You invoke it just before you close the application just by stating the name of the function. Did that help? Give it a try on a copy of your db and see what happens.

 

[ROMADOZ]

It would be easier to put it as a sub I think. Can it be in the same sub for my exit button? Like before the If Me.Dirty Then Me.Dirty = False line? I think I would have to pull the end function off though... right?

 

[RuralGuy]

Don't get too creative to start. Get it working first. Put the code in a standard module named basBackup. It will all be in one location so you can adjust the code until it does what you want. Then add the following RED code to the final exit, using your function name of course.

Private Sub btn_Exit_Click()
   On Error GoTo Err_btn_Exit_Click

   If Me.Dirty Then Me.Dirty = False
   [COLOR="Red"]MyBackup[/COLOR]
   DoCmd.Quit
Exit_btn_Exit_Click:
   Exit Sub
Err_btn_Exit_Click:
   MsgBox Err.Description
   Resume Exit_btn_Exit_Click

End Sub

 

[ROMADOZ]

I tried that, but got the compile error, expected variable or procedure, not module.

 

[RuralGuy]

You should have the Function in a standard module and do *NOT* name the module the same as the function!

 

[ROMADOZ]

got it... renamed. Now, if you would be so kind, define a standard module. It is in it's own module. In fact, it is the only module I have.

 

[RuralGuy]

Forms have their own Class Module and then there are the Standard Modules (Stand Alone) that you can create that are not attached to a form.

 

[ROMADOZ]

OIC... now I understand what you are saying. It is in it's own module, not in the class objects. I still get the same error. Here is my code on the form... followed by the code for the actual function. How and where do I set a reference to the "Microsoft scripting runtime"?


Private Sub btn_Exit_Click()
On Error GoTo Err_btn_Exit_Click
newDB
If Me.Dirty Then Me.Dirty = False
DoCmd.Quit
Exit_btn_Exit_Click:
Exit Sub
Err_btn_Exit_Click:
MsgBox Err.Description
Resume Exit_btn_Exit_Click

End Sub


Option Compare Database
Public Function BackupCopy()
'This function will allow you to copy a db that is open,
'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!
Dim fso As FileSystemObject
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
sSourcePath = "E:\"
sSourceFile = "training database.mdb"
sBackupPath = "E:\work stuff\"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"
Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set fso = Nothing
Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"

 

[ROMADOZ]

Also, how do I program something to run automatically on a set time? Specifically, I want to have a module run at midnight that checks a certain table for updates and then applies those updates to the records it applies to.

 

[RuralGuy]

OIC... now I understand what you are saying. It is in it's own module, not in the class objects. I still get the same error. Here is my code on the form... followed by the code for the actual function. How and where do I set a reference to the "Microsoft scripting runtime"?


Private Sub btn_Exit_Click()
On Error GoTo Err_btn_Exit_Click
newDB <<< What is this???
If Me.Dirty Then Me.Dirty = False
DoCmd.Quit
Exit_btn_Exit_Click:
Exit Sub
Err_btn_Exit_Click:
MsgBox Err.Description
Resume Exit_btn_Exit_Click

End Sub


...And the following function is not complete!
Option Compare Database
Public Function BackupCopy()
'This function will allow you to copy a db that is open,
'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!
Dim fso As FileSystemObject
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
sSourcePath = "E:\"
sSourceFile = "training database.mdb"
sBackupPath = "E:\work stuff\"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"
Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
Set fso = Nothing
Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sBackupFile, vbInformation, "Backup Completed"

While looking at the code you go to Tools>References and scroll down to Microsoft Scripting Runtime and put a check there.

 

[ROMADOZ]

Got the runtime thing. Where you ask "What is this?", that is the name of the module the function resides in... how am I supposed to call the module into action?

Second, what is missing in the function?

 

[RuralGuy]

You call the function by name and not the module where it resides. It should also *follow* the If Dirty, not come before it.

 

[RuralGuy]

Second, what is missing in the function?

...The End Function if nothing else!

 

[ROMADOZ]

so, backupcopy.newdb where newDB is the name of the module?

 

[RuralGuy]

THe Module name is not used in the syntax at all! Just use the Function name. It *must* be unique throughout your entire project.