Auto backup of both FE and BE using VBA

[lumpkinbd]

I have a database that has been split into a FE and BE. Recently we had a user delete 18 months of archived data from the BE. Luckily, IT was able to restore a previous version and retrieve the data. But now I am trying to set up an auto backup of both the FE and BE using an Auto Exec macro and VBA code. I have the code to back up the FE when opened, but I just don't know how to make the BE backup from the same VBA code. Here is the code I am using to back up the FE.

Option Compare Database
 
Public Function fMakeBackup()
 
    Dim Source As String
    Dim Target As String
    Dim retval As Integer
 
 
    Source = CurrentDb.Name
 
 
    Target = "S:\Databases\Backups\Open Obligations Action "
    Target = Target & Format(Date, "mm_dd_yy") & ".accdb"
 
    ' create the backup
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(Source, Target, True)
    Set objFSO = Nothing
 
 
End Function
 
 

I am not even going to claim that I figured that code out, I found it out in the interwebby, but could not find a solution to the BE problem. I am not very good with VBA, but I know it's the only way for Access to do an Auto back up. Any help would be greatly appreciated.

[zmbd]

I am surprised that the filescript object was able to copy the open front end, usually, while the front-end, is open, it not be able to be "backed-up" via VBA.

The back-end however, I use:
DBEngine.CompactDatabase zstrData, zstrBkp
Where zstrData is the full name and path of the backend
and zstrBkp is the full name and path of the copy of the backend

Usually, one can do this method; however, I have had occasions where multiple users were in the database and that will cause you issues if the records are locked; thus, you have to error trap for that... at some point, all of the users are logged out and the code will succeed when the last person closes their front-end.

I find it much better to ensure that IT backs-up the folder on a weekly basis. Mission critical information I back-up myself too; however, I don't have as big a harddrive as they have tapes (or whatever is being used now :) ).

As for the front-end - because each user should be using their own copy on their own client-pc, all you should need to do is keep a copy on the network in a restricted read-only directory. There are various threads here on Bytes covering different methods of deploying revised front-ends.

As for being able to delete the data...
+ If the user is in the tables, slap their fingers. They should never be directly using the tables.
+ If your table relationships are set up to enforce with Cascade delete option... this is why I never use that option. The parent cannot be deleted so long as there are related child record; thus, without the cascade delete, the child records are more difficult to remove (not impossible, just takes a bit more effort).
+ Batch delete... in the forms, I usually have the user enter a random 3 character string, or their "PIN" to confirm batch deletes. Often, if it's a lot of data, I'll export it to a second database named: {queryname}_{date}_{userID} then do the batch delete.
+ Once the design phase is done, going into Alpha or Beta testing, I usually remove any delete-action-queries from the stored queries. Instead I create these in VBA as needed.

[lumpkinbd]

Damn, until I read your reply I never even thought of the fact that the code I have currently will just backup the users copy of the FE and not the actual FE. Epic fail. The FE is in fact in a secure location and when I make any changes to it, I just email all users a new copy of the FE. And IT was able to recover a previous version of the DB because it is backed up, but our IT is SLOW and I would rather have the information backed up myself so they don't need to be involved.

Where exactly do you use that code for the BAckEnd? Do you just put that in a module, I'm sorry, I'm still working through this whole VBA thing.

Thanks for the help thus far.

[zmbd]

I have a command button to close the application and the mainform has an on_close event that cancels the close unless the commandbutton has set a flag indicating that the backup attempt was attempted.