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!

 

[ranman256]

I'd have a form running with the timer. when the timer hits, backup the db:

vSrc = "\\folder\myDB.mdb"
vTarg = "\\folder2\bak\myDB" & format(date,"yyyymmdd-hhnnss") & ".mdb"
filecopy vSrc , vTarg

Users dont have to be out of the db to copy. It still copies.

 

[legalhustler]

I'd have a form running with the timer. when the timer hits, backup the db:

vSrc = "\\folder\myDB.mdb"
vTarg = "\\folder2\bak\myDB" & format(date,"yyyymmdd-hhnnss") & ".mdb"
filecopy vSrc , vTarg

Users dont have to be out of the db to copy. It still copies.

Yeah I guess that's fine too. Do you have the entire code with that incorporated?

 

[legalhustler]

Anyone?

 

[ranman256]

That's all the code. The databse is backed up to your folder with a timestamp.

if you make a new MODULE, put the code below in it

public function BackupDB()

dim vSrc, vTarg

vSrc = "\\folder\myDB.mdb"
vTarg = "\\folder2\bak\myDB" & format(date,"yyyymmdd-hhnnss") & ".mdb"

filecopy vSrc , vTarg

Then make a macro
in the macro event put RUNCODE BackupDB()you can backup by double-clicking the macro.


</PRE>

 

[legalhustler]

That's all the code. The databse is backed up to your folder with a timestamp.

if you make a new MODULE, put the code below in it

public function BackupDB()

dim vSrc, vTarg

vSrc = "\\folder\myDB.mdb"
vTarg = "\\folder2\bak\myDB" & format(date,"yyyymmdd-hhnnss") & ".mdb"

filecopy vSrc , vTarg

Then make a macro
in the macro event put RUNCODE BackupDB()you can backup by double-clicking the macro.

I want to avoid opening the database, double clicking the macro and backing the database. I would like the database to backup automatically on it's own at a specific date/time.

 

[legalhustler]

I still need some help with this. I would like database to backup automatically in a network folder daily (preferable at 6pm) and replace the existing backup so that there is no duplicates and only one backup file is in the netowork folder.

 

[TerryHogarth21]

Are you familiar with scheduling a VBS through the Windows Task Scheduler?
Schedule a task - Windows Help


Here's how I normally do this :

1) Take ranman256 function and put that into your Access DB Module.
2) Copy and paste this below into NotePad - make sure to change the variable the location of your DB path and Function name

 Dim db
Dim strDbName
dim strMacro
strMacro = "Your Function Here"
strdbName = "Your Path Here for DB"
Set acc = createobject("Access.Application")
 
'this temporarily changes the macros security to low to avoid messages.
'only works with access 2000 onwards
if acc.syscmd(7) >= 10 Then
acc.AutomationSecurity = 1
End if
 
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
acc.run strMacro
acc.quit

3) Save that Notepad file as something but make sure to add the extension .VBS (ie. Test.VBS)
4) Test to see if your script file works by double clicking it. It should run that line of code in the script file to open up access, run your function, and then close out the database.
If this doesn't work make sure that the function or code in Access is correct or post what message you get.

5) Create a Task in Windows Task Scheduler
6) For Trigger- make sure your trigger is set based on the time that you want and the options to repeat etc.(should be self explanatory)
7) For Actions- make sure you search for the name of the VBS file and attach it to the Task.
edit anything else that you need for conditions or what not.

I do this all the time but I'm not here at 6PM EST but you could technically leave your computer logged on or sleep and check on the options for the conditions. You could even have your computer shutdown after it runs this too.
Note the script stuff won't run if your computer is shut off - in which you set up the Task scheduler.

The other thing I've done in the past is get IT's help to which they just take your VBS file and add it to run along with theirs over night. (you'll need buy in on this though)

Hope that helps you.

 

[legalhustler]

Ok. I finally got it work. Here is the code to back up your ENTIRE database objects:

Step1:

Option Compare Database
Option Explicit

Function AutoRun() 'called thru macro AutoExec: RunCode AutoRun()
    Dim sFile As String
'Change the file path to your desired path
    sFile = "C:\Users\Bob.Smith\Desktop\"
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile sFile
    BackUp
    End Function


Sub BackUp()
    Dim dTime As Date
    On Error Resume Next
'Change time value to desired time or you can change the value in the prompt screen
    dTime = InputBox("Create a backup at", , TimeValue("6:00PM"))
    If Err.Number <> 0 Then Exit Sub
    Do Until Time = dTime
        DoEvents
    Loop
   
    Dim sFile As String, oDB As DAO.Database
    'if DAO.dll does not load, then find
    'ACEDAO.dll in Program Files (64-bit machine) or MS Office AC DB Engine Object (see Tools >> References)
    
    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".mdb"
    If Dir(sFile) <> "" Then Kill sFile
    Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
    oDB.Close
    DoCmd.Hourglass True
    
    'backup all Tables
    Dim oTD As TableDef
    For Each oTD In CurrentDb.TableDefs
        If Left(oTD.Name, 4) <> "MSys" Then
               DoCmd.CopyObject sFile, , acTable, oTD.Name
               'OR: DoCmd.TransferDatabase   acExport, "Microsoft Access", sFile, acTable, oTD.Name, oTD.Name
        End If
    Next oTD
    
    'backup all Queries
    Dim oQD As QueryDef
    For Each oQD In CurrentDb.QueryDefs
        If Left(oQD.Name, 1) <> "~" Then
               DoCmd.CopyObject sFile, , acQuery, oQD.Name
        End If
    Next oQD
    
    'backup all Forms
    Dim oForm As Object
    For Each oForm In CurrentProject.AllForms
         DoCmd.CopyObject sFile, , acForm, oForm.Name
    Next oForm
    
    'backup all Reports
    Dim oReport As Object
    For Each oReport In CurrentProject.AllReports
        DoCmd.CopyObject sFile, , acReport, oReport.Name
        Next oReport
        
    'backup all Modules
    Dim oMod As Object
    For Each oMod In CurrentProject.AllModules
         DoCmd.CopyObject sFile, , acModule, oMod.Name
    Next oMod
    
    'backup all Macros
    Dim oMac As Object
    For Each oMac In CurrentProject.AllMacros
        DoCmd.CopyObject sFile, , acMacro, oMac.nae
    Next oMac
        
    DoCmd.Hourglass False
'Change this message as needed
    MsgBox "Backup is stored in the same folder"
End Sub

Step 2:

Create a Macro (Name it something like AutoExec)

Select: Run Code
Function Name: AutoRun()

Whenever you open your database it'll prompt you to do a backup. If you can either click Ok or Cancel if you do not want to do a backup.

 

[legalhustler]

Are you familiar with scheduling a VBS through the Windows Task Scheduler?
Schedule a task - Windows Help


Here's how I normally do this :

1) Take ranman256 function and put that into your Access DB Module.
2) Copy and paste this below into NotePad - make sure to change the variable the location of your DB path and Function name

 Dim db
Dim strDbName
dim strMacro
strMacro = "Your Function Here"
strdbName = "Your Path Here for DB"
Set acc = createobject("Access.Application")
 
'this temporarily changes the macros security to low to avoid messages.
'only works with access 2000 onwards
if acc.syscmd(7) >= 10 Then
acc.AutomationSecurity = 1
End if
 
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
acc.run strMacro
acc.quit

3) Save that Notepad file as something but make sure to add the extension .VBS (ie. Test.VBS)
4) Test to see if your script file works by double clicking it. It should run that line of code in the script file to open up access, run your function, and then close out the database.
If this doesn't work make sure that the function or code in Access is correct or post what message you get.

5) Create a Task in Windows Task Scheduler
6) For Trigger- make sure your trigger is set based on the time that you want and the options to repeat etc.(should be self explanatory)
7) For Actions- make sure you search for the name of the VBS file and attach it to the Task.
edit anything else that you need for conditions or what not.

I do this all the time but I'm not here at 6PM EST but you could technically leave your computer logged on or sleep and check on the options for the conditions. You could even have your computer shutdown after it runs this too.
Note the script stuff won't run if your computer is shut off - in which you set up the Task scheduler.

The other thing I've done in the past is get IT's help to which they just take your VBS file and add it to run along with theirs over night. (you'll need buy in on this though)

Hope that helps you.

I'm going to also try your method as well.

In the below, what am I supposed to put for "Your Function Here"?

strMacro = "Your Function Here"

Also, do we need to put " " for both the lines after we change the info?

Thanks!