Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32

    Unanswered: Create Database Backup on close...

    I'm trying to make a backup of my Access database on close.

    I've tried replication, but that seems to need absolute paths, which is no good for my situation. For my situation, "filename" and "filepath" for the mdb/mde are variables. And to complicate things more, I need to be able to do this copy while the database is open.

    I can already capture the database close event, but the rest is a bit above my head.

    The main intent is when the LAST person using the mdb/mde exits, then the backup copy is made to the same path + "\Backup", or something to that effect. I really don't need to "detect" the last person though. If the copy function fails because the database is being written to, that is fine... at least the backup copy would happen (in theory, if I'm thinking about this correctly) when the database is closed by the last person.

    I found a previous post mentioning the FileCopy method. Nice and easy but won't let me copy the mdb while it was open (Permission Denied). I didn't even bother making the MDE and trying it again. Would that work with an MDE?

    For API here is what I have so far:

    Code:
    Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA" (ByVal lpExistingFileName As String, lpNewFileName As String, bFailIfExists As Boolean) As Long
    
    Function CopyDB()
    
      Dim lpExistingFileName As String, lpNewFileName As String, bFailIfExists As Boolean
      lpExistingFileName = CurrentDb.Name
      lpNewFileName = CurDir() & "Test " & Replace(CurrentDb.Name, CurDir(), "")
      bFailIfExists = False
    
      CopyFile lpExistingFileName, lpNewFileName, bFailIfExists
    
    End Function
    This is basically a direct copy from a function that you guys helped me with in the past + my limited knowledge of visual basic. I can at least get the variables to respond when I use MsgBox() but getting any copy to work just won't happen.

    I googled as much as I could, but I couldn't find any API specific examples for use with MS Access, and all other examples I found either didn't make sense, were VERY long and complex, or didn't work.

    ======================

    I'm using Access 2007, but the format is Access 2002/03. I'm trying to keep as much compatability as possible but still be able to make the MDE application.

    We're on XP SP3 (for now) but will soon be switching to Vista (stuipd warranty expired).

    So... how close did I get with the API code? And are there any better ways to accomplish my idea?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    As for getting it in the same location, try something like:
    lpNewFileName = application.currentproject.path & "\" & currentdb.name & "_backup.mdb"

    I'm not sure how to copy an mdb/mde that you're currently in, have only tried to do an unaccessed file, but I'll keep checking.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32
    I know that I can use CTRL + C and copy an mdb/mde currently in use. What is the equivalent of that in code? Or is there a coding equivalent?

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I think it can be done with a File System Object, try:
    Code:
    Dim fso As Object
    Dim strOldPath As String, _
        strNewPath As String
    
    strOldPath = application.currentproject.path & "\" & currentdb.name & ".mdb"
    strNewPath = application.currentproject.path & "\" & currentdb.name & "_backup.mdb"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strOldPath, strNewPath
    Set fso = Nothing
    Mind you, that's untested code, but give it a shot.
    Me.Geek = True

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Maybe copy it to a different machine on the network too?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    By the way, you may also want to make the backup name more specific to avoid trying to write a backup when a file already exists with the same name. So maybe try,

    Code:
    Dim fso As Object
    Dim strOldPath As String, _
        strNewPath As String
    
    strOldPath = application.currentproject.path & "\" & currentdb.name & ".mdb"
    strNewPath = application.currentproject.path & "\" & currentdb.name & "_backup" & format(now(),"yyyymmddHhNnSs") & ".mdb"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strOldPath, strNewPath
    Set fso = Nothing
    Me.Geek = True

  7. #7
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32

    Thanks!

    Thanks nckdryr it worked beautifully! Now the only thing left (to keep things neat and clean) is to check and see if the file is already being copied, and if so then Halt.

    EDIT: Either that, or check to se if fso is already set, and if it is then halt. It seems that when you set fso = Nothing ... when I check IsNull(fso) it is still False. Is fso set as a global variable? And if so, how would I check to see if it is already set before copying?

    Also, is it possible to make the code so that it doesn't lock up the machine while copying? (not as important).

    Worst case scenario, the function you gave me works great as is (except that application.currentproject.path and currentDb.Name return the same thing but I fixed that part.)

    Thanks again!

    garethdart Not a bad idea, but I'm working off a network drive, and the last thing I need is to have many copies floating around.
    Last edited by TheArkive; 06-08-09 at 12:17.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Wink Backup on another drive / machine

    The reason I suggested another drive, is that if you are going to back it up, what happens if the drive your main copy on:-

    Corrupts or crashes
    is stolen or destroyed by fire
    gets abducted by Aliens or unknown entities!#;

    ...You lose the original file and your backup...

    As long as you can live with these three equally possible scenarios just ignore...!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by garethdart
    The reason I suggested another drive, is that if you are going to back it up, what happens if the drive your main copy on:-

    Corrupts or crashes
    is stolen or destroyed by fire
    gets abducted by Aliens or unknown entities!#;

    ...You lose the original file and your backup...
    Also, I wouldn't rule out the possibility of pirates and/or ninja-vikings. Those guys really know how to plunder.

    For my information (since I'm not as familiar with the project as you are), why would you need to check to see if it's already being copied?

    Also, the code should only be locking the Access application, I think you should still be able to run other applications.
    Me.Geek = True

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why not just back the database up using normal methods? I've never even considered having an Access database "back itself up".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32
    nckdryr: yes check to see if it is already being copied, or check to see if the "fso" variable has already been set (thus signifying that the copy funciton has already been started by a previous user) ... or something like that.

    garethdart: thanks for the idea, I know what you mean, though here is our setup--
    -- We have a 1TB drive (RAID) i think, and incremental backups are made about once a week I think (or once a month). This is decent for now. As for all our other computers/workstations, even though I'm the "computer guy" I don't have my own workstation to make this backup, and I would have to be physically at the machine to make it happen (ie. the OnClose event). Furthermore, I'm trying to make this thing "deployable" to other units (I'm in the Army), and I would not know the layout of the network for those other units. Every Army Post is different. Since the Army is not concerned with making admin work at my level easier, I'm coming up with a home-brew solution... and this is the best I can do, for now. (too bad...)

    StarTrekker: please enlighten me. I know that I can use replication and synchronization, but those methods require absolute paths, am I correct? I need a dummy proof way to make this application self-sufficient, with some kind of a backup strategy. Given what I described to garethdart could you suggest something better that would NOT require anyone else to understand how Access works? If you think I am "over-automating" please say so. I've been guilty of that before.

    EDIT: one last thing. nckdryr, the code works fine as an mdb, and yes Access is the only application being locked. I can't convert it to an MDE though, probably beause the OnClose event fires as the mdb closes and re-opens.

    I had an idea to pass the code over to the command line (to "explorer.exe") with the progress bar and such, achieving the same effect, and not slowing down Access, nor the user. My reason for this is that my app by default takes up the full screen and users won't be able to access their desktop until Access closes. I suppose I could script a batch file :-p and achieve this effect. I admit, I'm trying to be lazy :-P ... though if this idea continues to cause more problems than it solves then I'll just get off my lazy butt and do what is within my means :-P
    Last edited by TheArkive; 06-09-09 at 15:52.

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    As for checking to see if it's already been backed up, I would again suggest as I did in Post #6 to uniquely name the backup file, thus making the need to check for the name a moot point. Else what happens when User A exits the db and it backups correctly, and then User B comes along, exits the db and has an error when it's backing up? Then your backup would be corrupted, thus negating the purpose of having a backup in the first place.

    Also, as far as the locking part goes, why not just minimize the application before trying to backup (and anything else you may have in your closing procedure)?
    Last edited by nckdryr; 06-09-09 at 18:52.
    Me.Geek = True

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I backup using VBS or BAT files, fired by Task Scheduler, so I doubt I can be of any more help on this one.

    If I had to I would try using FileSystemObjects and just try to copy the file that way.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32
    nckdryr: too easy, I can't believe I didn't think of that in the first place. As for the checking, I'm trying to have a backup without needing to clean out the old ones. I understand what you mean about a corrupt backup, but I don't forsee other "Administrators" of this database doing that kind of upkeep when needed.

    StarTrekker: I kinda figured that was the most reasonable way to go. I won't be able to use the task scheduler though, but I got an idea of where to start.

    Thanks for your input guys. Ya helped me set my mind straight.

  15. #15
    Join Date
    May 2005
    Posts
    1,191
    If you still want to proceed with checking for the file first, the fso has the FileExists property which I think might be of use to you.
    Me.Geek = True

Posting Permissions

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