Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for Backup Strategies (Access 2002)

    We need to include a backup module in the Access 2002 VBA application that we are writing. When the system goes live, it will be split into a front end (programs) mdb and a back end (data) mdb. The backup file will be written to CDRW and its name should include the current business date which is stored in one of the tables. The user requires a "hands off" approach. In other words, the less action that the staff needs to take, the better.

    I was thinking of copying the tables via a DoCmd.CopyObject statement, one for each table. I can copy all tables this way. That is not a problem. My question is: Is there a fast and easy way to copy the entire back end mdb file, in one shot, from the front end mdb?

    Thanks.
    Carol W.

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    As long as the backup is performed from the frontend, from an unbound form with no bound forms open, you can use the "FileCopy" statement to copy the backend to another location.

    HTH
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Here is a sample routine that should work for you:

    Public Sub BackUp()
    On Error GoTo Err_Backup
    Dim strMsg As String, strTitle As String
    Dim strSource As String, strDest As String
    Dim strError As String

    strMsgComplete = "The Backup of the Database was Sucessful."
    strTitleComplete = " Backup Complete"

    BeginBackup:
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Ricky:

    Thank you so much. Once again, you have come to my rescue! This is exactly what I was looking for. I tried the basic code you provided and it works perfectly.

    It's curious but I don't see a reference to FileCopy in VBA Help. That is part of what threw me off. Do you happen to know why there is no reference in Help?

    Thanks, again.
    Carol W.

  5. #5
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    FileCopy Statement is in A97 Help, but not in A2K or higher. I have to keep Help file of all versions just in case.

    TIm K.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Thanks for the info. Tim.
    Carol W.

  7. #7
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Hmmmm ........
    I just took a look in my Access 2002 help and went right to FileCopy. It is in the VBA help files. To access it you must be in the VBA code window then cilck on help from that window. This is another one of Microsoft's "Great Ideas" I guess .... You need help to work with the help files .... LOL

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    I stand (sit) corrected! I was looking in VBA Help but I was using the Index tab. It is not listed in the index, at least as far as I can tell, but it is listed under Statements on the Contents tab. Go figure!

    Thanks.
    Carol W.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    I posted this question several months ago and tried the solution posted at that time on a non split database. It worked. Now I am attempting to try it again, but this time I split my front end mdb file (using the Splitter wizard). Let's call the original file a.mdb. The wizard created a_be.mdb. I also have a linked table in the unsplit mdb. Let's says that it refers to b.mdb.

    Here's the problem: When I now try to use filecopy on a_be.mdb it says "permission denied" (At the moment, this was tried on Win ME. The target OS will be Win 2K Pro but haven't tried it there yet). When I try it on b.mdb it works. I noticed in Windows Explorer that there is a lock file called a_be.ldb open when the front end (a.mdb) is open but there is no ldb file for b.mdb. I assume that's why I am getting "permission denied" when trying to copy the back end file.

    Any suggestions for copying a_be.mdb? Thanks, in advance.
    Carol W.

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

    Re: Looking for Backup Strategies (Access 2002)

    What does a lock file (or its absence) on b.mdb have to do with copying a_be.mdb? The simple answer is that you can't copy an mdb with an active lock on it.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Access won't copy a locked file but a batch file seems to do it OK and you can call the batch file from Access.

    HTH

    Peter

  12. #12
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Thanks for the reply.

    I mentioned the lock file on b.mdb to support my theory (now confirmed) that an mdb with a lock file on it cannot be backed up. b.mdb does not have a lock file associated with it and can be filecopied. I'm sorry if I did not make that clear.
    Carol W.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Thanks, Peter. I'll give it a try.
    Carol W.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    With regard to the title of the post

  15. #15
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for Backup Strategies (Access 2002)

    Thanks for the thought, Rupert. I'll definitely keep it in mind.

    BTW, the bat file idea works very nicely in Win 2K Pro. It even closes the DOS Window when it's done. It does not close the DOS window in Win ME.
    Carol W.

Page 1 of 2 12 LastLast

Posting Permissions

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