Automate your FE backup

[Pat Hartman]

Have you ever lost a day's worth of development work or even an hour? It is extremely painful. I developed some code years ago to protect me from myself and I organized it and put it all together for you to use. Once you import the objects and set up the two tables that hold the paths you want to use, all you need to do to make a backup is to close the application and answer yes to the prompt. If you want to, you can even run the backup manually by adding a button to a form.

There are two types of backups included. Use one or both. What prompted me to create this code was a database that keep corrupting. Everything would be working fine. I'd close the database and when I tried to open it, it was corrupted. My solution was to create the procedure that exports all the objects to text when I closed the database. The code checked the login and only prompted if it was me so the users never saw the backup prompt because they never changed the FE. Making a full file copy didn't work because the copy was also corrupted. This method has saved my bacon on several occasions. One just yesterday when I was testing the code. I had just run the backup to text. Then I made a different change and was sloppy about it. I made the mistake of editing code while it was running and I corrupted the database Lucky me, I had a backup

The second type of backup is the full file variety. I was teaching an analyst who had always used Excel how he could use Access to do preprocessing to clean up the downloaded data and replace his vLookups with joins so he could export a clean recordset to Excel. Using Access for the cleanup gave him TWO HOURS back into his day because that's how long the vLookup()s and other cleanup macros took to run every morning. He eventually started doing more complicated stuff but he was lacking in discipline and kept losing his database. So, I wrote him versioning code that displayed the date of the last change in big red letters when he opened the db AND popped up a message if it wasn't monday and the update date was more than 24 hours ago. I also made a full file backup for him that went to two directories. One on the server and the other on his local drive.

The database also includes a menu and shows how you can open one form and hide the form that opened it to reduce clutter and confusion. When the second form closes, the first one reopens. There is common code added to a couple of events to handle this if you choose to use it.

The db is built with the intention of using it as the base for each new application so when you want to start a new database, you copy this template and then add your new objects to it. Otherwise, you can add the objects needed for the backup to an existing database.

 

[Ranman256]

paste the code into a module, then run: BackupDb
it makes a time-stamped copy of the db you're in to the folder assigned in: kTargDir

Public Function BackupDb()
Dim vDir, F, vExt, vTarg, vDb
Dim vTargDir, vSuffx
const  kTargDir = "c:\backup\"

    vDb = CurrentDb.Name
     'BackupLib & vFile
   If vDb <> "" Then
        getDirName vDb, vDir, F
 
      vExt = Mid(F, InStrRev(F, "."))
   
       vSuffx = "_Backup" & TimeStamp(3) & vExt       
  End If
    
         'FRONT END APP
    vDb = CurrentDb.Name
    getDirName vDb, vDir, F
    vTarg = Trim(kTargDir) & F & vSuffx

   Copy1File vDb, vTarg
 
   MsgBox "Done", vbInformation, "Backup"

End Function


'given filepath, passes back: Dir name , filename
Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
    'psFilePath: full file path given
    'prvDir : directory name output
    'prvFile: filename only output
Dim i As Integer, sDir As String

i = InStrRev(psFilePath, "\")          'not available in '97

If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
End If
End Sub


Public Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim FSO
On Error GoTo errCopy
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile pvSrc, pvTarg
Copy1File = True
Set FSO = Nothing
Exit Function
errCopy:
MsgBox Err.Description, , Err
End Function

 

[Alt]

Hi Ranman256,

I got an error on "TimeStamp(3)"... Undefined Sub or Function. Shall I add a new reference?

Thanks for sharing your code.

 

[Pat Hartman]

@Alt If you have a problem with code posted by Ranman, Use @Ranman256 to get his attention. Do NOT use the Sample Database forum to post questions. I will move the thread if I can. Otherwise, I will just lock this thread and you'll have to repost in one of the other forums. Add a link to this thread. @Ranman256 never should have posted his alternate solution here anyway.