If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Scheduled backup of multiuser access db

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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.

Reply With Quote
  #2 (permalink)  
Registered User
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.
Reply With Quote
  #3 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,296
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
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote
  #4 (permalink)  
Registered User
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?

Reply With Quote
  #5 (permalink)  
Registered User
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.

'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
     WScript.Echo "Unable to locate this file: " & sOriginFolder & dbFiles(i) & "." _
       & vbCrLf & "Evaluation is required."
  End If


Last edited by JerryDal; 06-16-10 at 13:48. Reason: spelling
Reply With Quote
  #6 (permalink)  
Registered User
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
Reply With Quote
  #7 (permalink)  
Registered User
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.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On