Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Backup database from code (Access 2002/XP)

    I am using the following code to enable an Access file to be backed up with just the click of a button from a form screen within a different database. The code does what it is supposed to do except that it leaves a black DOS window on screen that has to be manually closed before you can even see the message box telling you what the name of the copied file which includes the current date and time. Does anyone know how I can modify the code to automatically close the DOS window?

    Private Sub cmdBackup_Click()
    Dim db As DAO.Database
    Dim CopyString As String
    Dim SourceFile As String
    Dim DestFile As String
    Dim TimeStamp As String

    Set db = CurrentDb

    TimeStamp = Format(Now(), " yyyy-mm-dd hh-nn")
    SourceFile = Chr(34) & "N:SASIXPAttendanceOAR_be" & ".mdb" & Chr(34)
    DestFile = Chr(34) & "N:SASIXPAttendanceBackupOAR_be" & TimeStamp & ".mdb" & Chr(34)
    CopyString = "CMD.EXE /C COPY " & SourceFile & " " & DestFile
    Call Shell(CopyString, vbNormalFocus)
    MsgBox "when backup is finished, the filename will be " & DestFile

    End Sub
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Backup database from code (Access 2002/XP)

    You don't need to use Shell, you can use the FileCopy instruction:

    FileCopy SourceFile, DestFile

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup database from code (Access 2002/XP)

    I replaced the line in my code Call Shell(CopyString, vbNormalFocus) with FileCopy SourceFile, DestFile

    When I tried running it, I got a run-time error 52 - bad file name or number

    I removed the chr(34) from the Source File and DestFile and tried running it again. This time I got a run-time error 70 - permission denied.

    The code is located in the front end and the database being backed up is the backend. It copied the file without errors using the code I originally posted that I got from www.accessdatabasetips.com/copying-a-file.html

    The problem was the DOS window that has to be manually closed.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Backup database from code (Access 2002/XP)

    Perhaps someone who remembers how MS-DOS worked can help you.

  5. #5
    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 database from code (Access 2002/XP)

    Judy,
    Here's another way to try this:
    1) Create a module as such:
    Option Compare Database

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

    'Ensure you name it something other tan CopyFile

    2) Use this code for the OnClick procedure of your button:


    If Dir("<font color=red>205.110.92.33sharedatabasenew_folderBack up" & Format(Date, "yyyymmdd"</font color=red>)) = "" Then
    retval = CopyFile("<font color=blue>205.110.92.33sharedatabasenew_foldertab les.mdb</font color=blue>", _
    "<font color=red>205.110.92.33sharedatabasenew_folderBack up" & Format(Date, "yyyymmdd") & ".mdb"</font color=red>, 1)
    MsgBox "The database has been archived"
    Else
    MsgBox "Copy failed - the database has already been archived"
    End If


    The highlighted portions are those that can be changed, to identify what file to backup, and where to back it up to. The blue text identifies the source file, while the red text identifies the destination. Note that I have appended a date to the destination file, to indicate the backup date.
    ____________________________
    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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Backup database from code (Access 2002/XP)

    The VBA FileCopy Command cannot copy a file while it is use, while the api FileCopy can.
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup database from code (Access 2002/XP)

    Hi Judy,

    When I tried running your (original) code it ran without any problems, and without leaving the command prompt window open. Since I haven't been able to replicate the problem I can't guarantee this will solve it, but it's worth a go...

    Private Sub cmdBackup_Click()
    Dim db As DAO.Database
    Dim CopyString As String
    Dim SourceFile As String
    Dim DestFile As String
    Dim TimeStamp As String

    Set db = CurrentDb

    TimeStamp = Format(Now(), " yyyy-mm-dd hh-nn")
    SourceFile = Chr(34) & "N:SASIXPAttendanceOAR_be" & ".mdb" & Chr(34)
    DestFile = Chr(34) & "N:SASIXPAttendanceBackupOAR_be" & TimeStamp & ".mdb" & Chr(34)
    CopyString = "CMD.EXE /C COPY " & SourceFile & " " & DestFile <span style="background-color: #FFFF00; color: #000000; font-weight: bold">& Chr(10) & "Exit"</span hi>
    Call Shell(CopyString, vbNormalFocus)
    MsgBox "when backup is finished, the filename will be " & DestFile

    End Sub
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup database from code (Access 2002/XP)

    I modified the code as per your suggestion. When I waited patiently (12 seconds), the dos window disappeared. The problem is the dos window is completely blank so I know it will throw the users into a panic. Is there anyway to hide the dos window while it carries out its job?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Backup database from code (Access 2002/XP)

    Try replacing vbNormalFocus with vbHide.

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup database from code (Access 2002/XP)

    Yes! That did the trick. Thanks a lot.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  11. #11
    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 database from code (Access 2002/XP)

    I was working through a new database and trying to remember how I'd set up an auto backup when I came back to this thread. I know you say it cannot do it, but it worked daily for me for over 6 months.... <img src=/S/hmmn.gif border=0 alt=hmmn width=15 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

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Backup database from code (Access 2002/XP)

    Jeremy, you are using the API call, not the VBA function. That's why it worked
    Charlotte

  13. #13
    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 database from code (Access 2002/XP)

    Ohh...well then, that makes sense. Thanks Charlotte
    ____________________________
    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

Posting Permissions

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