Results 1 to 13 of 13

Thread: Backup (2003)

  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Backup (2003)

    Is it possible to make a database automatically back itself up every time you close it? I know you can create a macro to backup the file but can it be automated when the file is closed?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backup (2003)

    If this is a database used by multiple users, backing up when the database is closed may not be a good idea - another user may try to open it while it is being backed up.

    There are many utilities, both free and commercial, that can perform scheduled backups, for example at night. (For example Automatic Backup from Candace Tripp's Visual Basic and MS Access Downloads, Access Autopilot | Backup, Archive, Compact Access Database Scheduler or Microsoft Access Database Compact, Repair, and Maintenance - Total Visual Agent).

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    I will look into those Hans, Thanks

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    I don't know if this will help or not, I pulled it off vba-programmers.com

    'Declarations
    Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA" _
    (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
    ByVal bFailIfExists As Long) As Long

    Private Sub cmdCopyFile_Click()
    Dim retval As Long
    retval = CopyFile("C:SourceFile.txt", "CestinationFile.txt", 1)
    If retval = 0 Then ' failure
    MsgBox "Copy failed -- CestinationFile.txt already exists."
    Else ' success
    MsgBox "Copy succeeded."
    End If
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    I will give it a shot. Thanks

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    Ok, so I tried the code I posted, and feel like a fool. It won't work for me. When I "click", I get: Compile Error: " Expected variable or procedure, not module"

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Private Sub Label94_Click()</span hi>
    Dim retval As Long
    retval = <font color=blue>CopyFile</font color=blue>("F:tables.mdb", "F:Tablesbackup.mdb", 1)
    If retval = 0 Then 'failure
    MsgBox "Copy failed - - Destination file already exists"
    Else 'success
    MsgBox "Copy Succeeded"
    End If
    End Sub


    Next part of my question...in this line:

    ("F:tables.mdb", "F:Tablesbackup<span style="background-color: #FFFF00; color: #000000; font-weight: bold">$$</span hi>.mdb", 1)

    I would like to add the current date where the <span style="background-color: #FFFF00; color: #000000; font-weight: bold">$$</span hi> symbols are, in yyyymmdd format, is that possible?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backup (2003)

    1) You probably named the module CopyFile, this confuses VBA, since CopyFile is also a built-in function. Give the module another name.

    2) retval = CopyFile("F:tables.mdb", "F:Tablesbackup" & Format(Date, "yyyymmdd") & ".mdb", 1)

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    Hans,

    No dice, I now get the error "Sub or Function not defined"

    The module is named DBaseBackup.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backup (2003)

    Which word is highlighted when you get this error?

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    CopyFile
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backup (2003)

    Is the declaration of CopyFile from <post#=493120>post 493120</post#> in the same module as the code that calls it? If not, you must change the declaration from Private to Public.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    Hans,
    You were dead-on, all I needed to do was make it public. Thanks!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup (2003)

    FWIW the attached is somewhat based on an article by Helen Feddema. This is in ADO - so, as it stands, it will only run in Access 2000 & higher.<UL><LI>Save it into a Standard Module in your DB <LI>It requires a Reference to the Microsoft Scripting Runtime library<LI>It also requires you set up a Table called tblBackupinfo with 2 fields: SaveDate (Date/Time - yyyy/mm/dd) and SaveNumber (Number - Long Integer)<LI>All backups are saved into a(n automatically created subfolder) of the DB called Backups. (You may need to have IT set up the folder for you in advance.){li]The filename includes a yyyy-mm-dd suffix so backups automatically sort themselves into date order.<LI>You can rewrite the code behind a Switchboard to backup the DB automatically. Personally, I have set up the Switchboard option to enable you to backup without closing down or to choose whether you want to backup on closing down, as follows:<pre>' Exit the application.
    Case conCmdExitApplication
    BackupDB
    Select Case intReturn 'YesNoCancel
    Case 6 'Yes
    intReturn = MsgBox("Do you wish to close down?", _
    260, Application.CurrentProject.Name)
    If intReturn = 6 Then Application.Quit
    Case 7 'No
    Application.Quit 'shut down Access altogether
    Case Else 'Cancel
    End Select</pre>
    [/list]HTH
    Gre

Posting Permissions

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