Creating Access Backup Database

[IrishBob80]

Hi All,



Within work I have access application (Front & Back End Split) that is used by another 100 users but only my team have view of the back end database.

Issue is I need to share the Database with another team member in different office, who doesn't have folder access (and never can, IT issue). We both have access to another folder on different drive/server, what I need to do is automate Access to each night (at set time..etc.) to create a copy of current database and move that copy file across to this shared folder. It needs to be daily thing outside of office hours, I don't have spare machine or set pc so can't use windows scheduler for this.

So my Question is there some OnTime Event (like in Excel) that can copy the whole database and transfer to this other location at set time each day (i.e. when no one is in the office)?

• It needs to not lock out the live database, its possible to have one or two people using the front end to do work (at night time), so don't want to have an issues with them updating any records.
• Needs to be Automate, I have small team and can't be manually having to create the copy database by member of the team.
• Finally needs to be copy version not linked tables etc as don't want an load increases on the back end database.

Any thoughts on the best way to do this? Many Thanks

[pbaldy]

This may get messy, but I'll get it started. I'd lean towards a "utility" db run from Windows Scheduled Tasks. Forms have timer events, but if there could be multiple users that have the app open at the appointed time, you'll have to take steps to keep it from running more than once. I also don't like the idea of the timer event running all the time when the process only needs to happen once a day, and it will interrupt whatever they're doing when it runs.

Simply copying the back end file is probably out if you can't have everybody out of it. Might not matter, but I've never been comfortable copying a file that's open. If you can link to both the main and other back ends, you can simply run delete/append queries to copy the data, table by table.

[IrishBob80]

Thanks Paul,

Will look into the append write, still needs someone to engage the process our side, either that needs to form part of the auto open function with auto check function built in to ensure that process only happens once a day. If there is no easy solution then will see what IT team can do to assist, ideally I need spare machine as a lot of automation events can be trigger early morning so remove some of those BAU Morning checks.

Many Thanks Kevin

[pbaldy]

Like I said, you can use the timer event on a form, but that requires that the database and form are open at the appointed time. That still leaves you with the potential of it being done twice, so having to prevent that (date field in the back end that gets updated, checked before doing the process). If they're set up for it, IT could create a virtual machine for you. I run a bunch of automated imports and exports on a VM. Failing that, you could use Windows Scheduled Tasks on somebody's machine that works at night, and just warn them at such-and-such a time their computer will get taken over to run this task.

So we're clear, I'm not saying it can't be done the way you described, just that there are hurdles to doing it that way.