Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Scheduled backup of multiuser access db

    Hi all

    I'm not sure if I'm asking too much here but... I have a multiuser Access database: the server has two backend database files, plus a client front end. There are copies of the frontend running on a few (about 3) other PCs. All is working well, I can enter and read data anywhere.

    Now I need to get my backup sorted out. Ideally I'd like to backup the backend datafiles once a day (e.g. at 03:00) so I'm looking for a way to schedule a backup. I could pay a few $$ if I need to buy a tool - I'm also ok with writing some VBA if that's needed.

    What makes this potentially tricky is that this system runs literally 24/7. So if something determines that its time for a backup and the system is in use (yes, even at 03:00) then I guess the users have to be warned or thrown off or something.

    Anyone else cracked this one? I'm open to any suggestions.

    TIA

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well it isn't particularly a biggie - - it also isn't really an Access issue. One can copy the BE at any time without interrupting users. Go ahead and do a manual copy/paste of the BE anytime you want. I think what you are asking for is to automate that - and so one would look into TaskManager - or whatever is the equivalent of that on the type/version PC/server that holds the BE. Generally companies have a backup system that covers every file on the server so it isn't even a concern for the Access developer.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,410
    look for windows scheduler on a windows server or windows PC if you want to do it at a specific time at, say night.
    if you are on a linux server then a cron job should do the dirty deed
    if you have roughty toughty server backup software then place the job in that queue.

    as a precaution examine the first few backups to make sure that you cna actually restore from the backup (otherwise you are lulling your self into a flase sense of security), and repeat periodically thereafter

    there is a piece of software that can tell you what PC's, users are connected to a MDB... I know I got it from Access Developer books, and I'm sure it will be available on t'net. look for code with a guid of {947bb102-5d43-11d1-bdbf-00c04fb92675}. or buy the sybex developers handbook for your version of Access

    after that you are in the realms of writing your own mechanism if you want to do this when others are using the system. one technique is to open up a hidden form that runs on a timer. periodically that form should query a table and see if it should shutdown. you can make it a bit more spohisticated by gettign the GUI forms and reports to report back to the hidden form that they ahve been used... and decide if the application should be shutdown automatically after so many time units of inactivity
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2010
    Posts
    88
    Thanks for the comments ...

    NTC - both from my own understanding of how databases work and also from what I've read elsewhere, copying a live database file while users are busy using it is just asking for trouble ... and worse, bearing in mind these are backups, it could be hidden trouble that you don't find out about till you try to use the backup. Yes, it will often work - but that isn't good enough.

    I have come across the technique of using a hidden form to check to see if it's time to shutdown, and I might well go that way. In that case, as both of you have said, it's then not an Access problem anymore, you can use any old backup/scheduling solution to copy the file off.

    Or I could make it an Access problem - having decided that its time to backup, I could write VBA so that one client (there is one co-hosted with the backend) disconnects the backend files, checks that other clients are disconnected, then copies the file, and reconnects to the backend again. Any pointers to code that will do something like that?

    Thanks
    C.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    507
    I remember that in my old job, scheduling under Windows XP was not available in our network PC environment, so I figured out how to write code in vbScript that would log me off at the end of the day, and do the data base back up at that time. For consistency, you probably will want to schedule the daily back up if your company network allows scheduling.
    Below is vbScript code, which I have just created and tested, that could be modified to back up two data base files. You would need to apply your own file names and paths to make it work. You could modify the code to store backed up files in folders Day1, Day2...Day30, based on what the day of the month is. I used the days of the week as folder names. Destination folders are created if they do not exist.
    The extension of the file with this code must be "vbs", such as dbBackup.vbs
    Good luck.

    Code:
    'VBScript code to back up specific data base files
    Dim oFSO, sFile, oFolder, oFileCopy
    Dim sOriginFolder, sDestinationFolder, sOriginFilePath, dayOfWeek
    
     dbFiles = Array("db1.mdb","db2.mdb")
    
     dayOfWeek = WeekDayname(WeekDay(Date))
    
     sOriginFolder = "c:\Temp\SharedFolder\"
     sDestinationFolder = "c:\Temp\" & dayOfWeek
    
     Set oFSO = CreateObject("Scripting.FileSystemObject")
    
     'create destination folder if it does not already exist
     If Not oFSO.FolderExists(sDestinationFolder) Then
        Set oFolder = oFSO.CreateFolder(sDestinationFolder)
     End If
    
     For i = 0 To 1
    
      If oFSO.FileExists(sOriginFolder & "\" & dbFiles(i)) Then
        sOriginFilePath = sOriginFolder & "\" & dbFiles(i)
        Set oFileCopy = oFSO.GetFile(sOriginFilePath)
        oFileCopy.Copy (sDestinationFolder & "\" & dbFiles(i))
        WScript.Echo "Copying : " & Chr(34) & sOriginFolder & dbFiles(i) & " to " & sDestinationFolder
      Else
         WScript.Echo "Unable to locate this file: " & sOriginFolder & dbFiles(i) & "." _
           & vbCrLf & "Evaluation is required."
      End If
    
     Next
    Last edited by JerryDal; 06-16-10 at 13:48. Reason: spelling

  6. #6
    Join Date
    May 2010
    Posts
    601
    I use a backup system that can be scheduled.

    I also schedule a separate database to run some time before the scheduled back up that flags the back end as offline to prevent any users from logging in and set a flag for the front end to log out all users by a specific time

    The front end has code to detect that the back end is off line what time to have all users logged out. It kicks off idle users. The front end alerts an active user to log out. It will automatically log out all users and at the specified time.

    Another scheduled task runs after the backup to pout the back end on line. I have not done it, but it could alerts users when the database is available.

    TIP: I use an separate file to flag the back end as offline. I do not use something in the back end. I want to insure that the front ends do not have to "touch" the back end.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    I develop Access dbs every day - and routinely copy the BE or have the client copy the BE and email it to me so I can see the data that they have (that is causing the problem at hand).

    It is absolutely no problem to copy the BE. It does not cause interuption or corruption to the production BE. And no data in the copied BE is corrupted.

    Are you concerned about the theoretical issue of a user initiating a change at the exact same moment? - - - sure at the moment of backup any action after the backup moment isn't going to be in the db; but this isn't an issue unique to Access. It simply dictates how often one must backup.

Posting Permissions

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