Automatically Backing Up An Access Database at a Particular Time

[legalhustler]

I have an Access database that I would like to back up automatically either on the network drive (or an online storage place would be best...dropbox?). My database has tables, queries, forms, reports, macros, etc so the ENTIRE database needs to be backed up. I would like to backup of the database to occur at a specific time, say 1AM every day, or once a week, say Fridays at 1AM.

This is a multi-user database so before the back up runs, it will need to check if there are any users that has the database open, and if there are then it should automatically boot the user after 5 mins and then run the back up.

I've heard VBA or running some batch file will do the job. I have very little experience in both.


TIA!

 

[legalhustler]

Maybe you should post your edited version. I didn't actually test the vbs script so I can do that. My experience with VBS is that it doesn't use Dim and all variables are variants anyway.

See below. I think there is a small syntax that needs to be added or deleted. I only changed the 3 paths to your code.

'---------------------------------------------------
'Preconditions
'    A Backup folder has been created and the
'    value of destFolder below has been edited
'    appropriately
'
'    The value for Days_Back_To_Delete_Old_Backups
'    below has been set to the number of days that
'    you would like to keep backups
'Postconditions
'    Backups with a timestamped name have been
'    saved in the backup folder, and backups
'    older than the Days_Back_To_Delete_Old_Backups
'    setting have been deleted

On Error Resume Next
'---------------------------------------------------
'Create Objects
Set col = New VBA.Collection
Set FSO = CreateObject("Scripting.FileSystemObject")

'---------------------------------------------------
'User settings
'Days back after which delete backups
Days_Back_To_Delete_Old_Backups = 7

'Where to save backups
destFolder = "C:\Users\joe.smith\Desktop\MasterDB\MyBackUps"

'Databases to backup
col.Add "C:\Users\joe.smith\Desktop\MasterDB\BackEndDB.accdb"
col.Add "C:\Users\joe.smith\Desktop\MasterDB\FrontEndDB.accdb"

'---------------------------------------------------
'Go
'New Backups saved with naming convention _yyyymmdd.hhnnss.
If FSO.FolderExists(destFolder) Then
    For i = 1 To col.Count
        If FSO.FileExists(col(i)) Then
            Set f = FSO.GetFile(col(i))
            BackupFileName = destFolder & "\" & FSO.GetBaseName(f.Path) & "_" & _
                        Format(Now, "yyyymmdd.hhnnss") & "." & FSO.GetExtensionName
(f.Path)
            FSO.CopyFile col(i), BackupFileName, True
        End If
    Next
'Cleanup old backups
If FSO.FolderExists(destFolder) Then
    For i = 1 To col.Count
        '//Check for backups with same name, excluding timestamp
        BaseName = FSO.GetBaseName(FSO.GetBaseName(col(i)))
        For Each f In FSO.GetFolder(destFolder).Files
            If Left(FSO.GetBaseName(f.Path), Len(FSO.GetBaseName(f.Path)) - 16) = 
BaseName Then
                '//Parse timestamp in filename and convert to a real date
                d = FSO.GetBaseName(f.Path)
                d = Right(d, 15)
                d = DateSerial(CLng(Left(d, 4)), CLng(Mid(d, 5, 2)), CLng(Mid(d, 7, 2))) 
+ _
                        TimeSerial(CLng(Mid(d, 10, 2)), CLng(Mid(d, 12, 2)), CLng(Mid(d, 
14, 2)))
                '//Delete backup if it is older than the cutoff date for saving backups
                If Now - d > Days_Back_To_Delete_Old_Backups Then
                    FSO.DeleteFile (f.Path)
                End If
            End If
        Next f
    Next i
End If

 

[xenou]

Hi,
You have some line breaks in your code that aren't in mine. Put it into a vba module in Excel or Access and you'll see it immediately (they show up in red). Or just copy my code and start over. Or use a good text editor like Notepad++ that can show you line breaks.

As a general rule it is useful to "test" your vbs in a vba environment as the debugging tools are better. When it's working that's when I copy it to the text file (without the dims).

 

[legalhustler]

Hi,
You have some line breaks in your code that aren't in mine. Put it into a vba module in Excel or Access and you'll see it immediately (they show up in red). Or just copy my code and start over. Or use a good text editor like Notepad++ that can show you line breaks.

As a general rule it is useful to "test" your vbs in a vba environment as the debugging tools are better. When it's working that's when I copy it to the text file (without the dims).

I re-copied your code to Excel VBA module and looks like there is no (red) line breaks anymore once I changed the paths. However, I still get an error "Expected end of statement" when I try to execute the VBS file. Here is the code:

'---------------------------------------------------
'Preconditions
'    A Backup folder has been created and the
'    value of destFolder below has been edited
'    appropriately
'
'    The value for Days_Back_To_Delete_Old_Backups
'    below has been set to the number of days that
'    you would like to keep backups
'Postconditions
'    Backups with a timestamped name have been
'    saved in the backup folder, and backups
'    older than the Days_Back_To_Delete_Old_Backups
'    setting have been deleted

On Error Resume Next
'---------------------------------------------------
'Create Objects
Set col = New VBA.Collection
Set FSO = CreateObject("Scripting.FileSystemObject")

'---------------------------------------------------
'User settings
'Days back after which delete backups
Days_Back_To_Delete_Old_Backups = 7
'Where to save backups
destFolder = "C:\Users\joe.smith\Desktop\MasterDB\MyBackUps"
'Databases to backup
col.Add "C:\Users\joe.smith\Desktop\MasterDB\MyDB.accdb"
col.Add "C:\Users\joe.smith\Desktop\MasterDB\FrontEndDB.accdb"

'---------------------------------------------------
'Go
'New Backups saved with naming convention _yyyymmdd.hhnnss.
If FSO.FolderExists(destFolder) Then
    For i = 1 To col.Count
        If FSO.FileExists(col(i)) Then
            Set f = FSO.GetFile(col(i))
            BackupFileName = destFolder & "\" & FSO.GetBaseName(f.Path) & "_" & _
                        Format(Now, "yyyymmdd.hhnnss") & "." & FSO.GetExtensionName(f.Path)
            FSO.CopyFile col(i), BackupFileName, True
        End If
    Next
End If
'Cleanup old backups
If FSO.FolderExists(destFolder) Then
    For i = 1 To col.Count
        '//Check for backups with same name, excluding timestamp
        BaseName = FSO.GetBaseName(FSO.GetBaseName(col(i)))
        For Each f In FSO.GetFolder(destFolder).Files
            If Left(FSO.GetBaseName(f.Path), Len(FSO.GetBaseName(f.Path)) - 16) = BaseName Then
                '//Parse timestamp in filename and convert to a real date
                d = FSO.GetBaseName(f.Path)
                d = Right(d, 15)
                d = DateSerial(CLng(Left(d, 4)), CLng(Mid(d, 5, 2)), CLng(Mid(d, 7, 2))) + _
                        TimeSerial(CLng(Mid(d, 10, 2)), CLng(Mid(d, 12, 2)), CLng(Mid(d, 14, 2)))
                '//Delete backup if it is older than the cutoff date for saving backups
                If Now - d > Days_Back_To_Delete_Old_Backups Then
                    FSO.DeleteFile (f.Path)
                End If
            End If
        Next f
    Next i
End If

 

[legalhustler]

I tested your VBA code and it works fine but your VBS code gives an error. I can't seem to figure what the error is. If you could please fix it and re-post that would be great.

Thanks!

 

[xenou]

Hi,

Okay. We'll have to scrap the VBS. Apparently it doesn't have collections and that's a kind of show stopper for me, since without collections we have to use arrays, and to use arrays we have to be very precise with syntax, and that's not much fun.

Here's how I'd do this, then, instead:

1) Create a utility database

2) Put the VBA code in the utility database, in standard module. Be sure to make it a PUBLIC Sub(). i.e.

Public MySub()
     Debug.Print "This is a Public Sub!"
End Sub

3) Now you can call create a small vbs script to open your utility database and run your vba code:

VBS Script Code (put in a text file with a .vbs extension)

On Error Resume Next
Call BackupDBs

'________________
Sub BackupDBs()
    
		
		Set objAccess = CreateObject("Access.Application")
		With objAccess
			.OpenCurrentDatabase "C:\MyUtilityDB.mdb", False
			ret = .Run ("MySub")
		End With

End Sub[B][/B]

 

[legalhustler]

Hi,

Okay. We'll have to scrap the VBS. Apparently it doesn't have collections and that's a kind of show stopper for me, since without collections we have to use arrays, and to use arrays we have to be very precise with syntax, and that's not much fun.

Here's how I'd do this, then, instead:

1) Create a utility database

2) Put the VBA code in the utility database, in standard module. Be sure to make it a PUBLIC Sub(). i.e.

Public MySub()
     Debug.Print "This is a Public Sub!"
End Sub

3) Now you can call create a small vbs script to open your utility database and run your vba code:

VBS Script Code (put in a text file with a .vbs extension)

On Error Resume Next
Call BackupDBs

'________________
Sub BackupDBs()
    
        
        Set objAccess = CreateObject("Access.Application")
        With objAccess
            .OpenCurrentDatabase "C:\MyUtilityDB.mdb", False
            ret = .Run ("MySub")
        End With

End Sub

Just curious - why shouldn't I put the VBA code in one of the active FrontEnd or BackEnd databases and have the VBS script run the VBA code from one of those databases rather than creating a third (utility) database? Just seeing if there is down side to that.

Yeah I'm reading some stuff and hopefully I can modify/fix your original VBS script. Will post it if I am successful.

Thanks.

 

[legalhustler]

I found a similar script and think we could use it modify your original VBS script.

bat script to delete file older than 7 days - Stack Overflow

 

[legalhustler]

Check this too:

Delete files older than X days

 

[xenou]

Just curious - why shouldn't I put the VBA code in one of the active FrontEnd or BackEnd databases and have the VBS script run the VBA code from one of those databases rather than creating a third (utility) database? Just seeing if there is down side to that.

Mainly I use a "utility" database because I find it a convenient place to put a routine that is not related to any one particular databases but is used to run adminstration procedure (such as backups) on all my databases. However, there are certainly lots of ways to set this up. You don't need the VBS script either, if you'd rather do without it. I had all my scheduled tasks like this kicked off by vbs scripts so it was convenient for me to stick to that approach, and keep all my scripts in one place.