Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 49

Thread: Automatically Backing Up An Access Database at a Particular Time

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatically Backing Up An Access Database at a Particular Time

    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!

  2. #2
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,767
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    I'd have a form running with the timer. when the timer hits, backup the db:
    Code:
    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.

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    Quote Originally Posted by ranman256 View Post
    I'd have a form running with the timer. when the timer hits, backup the db:
    Code:
    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?

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    Anyone?

  5. #5
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,767
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    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
    Code:
    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.


    Last edited by ranman256; Aug 11th, 2014 at 03:59 PM.

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    Quote Originally Posted by ranman256 View Post
    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
    Code:
    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.
    Last edited by legalhustler; Aug 11th, 2014 at 04:24 PM.

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    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.

  8. #8
    Board Regular
    Join Date
    Mar 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    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
    Code:
     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.

  9. #9
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

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

    Step1:

    Code:
    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.
    Last edited by legalhustler; Sep 11th, 2014 at 12:07 PM.

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,039
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically Backing Up An Access Database at a Particular Time

    Quote Originally Posted by TerryHogarth21 View Post
    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
    Code:
     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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •