Module to Auto Backup Access 2007 Database? (1 Viewer)

[davidb88]

Hi Forum -

I have tried numerous searches for this topic but can't find anything specific for Access 2007. I would like to be able to write a script in my Access 2007 database to create an automatic backup in a specified folder at a specific time each night (i.e. 9 pm every night) and delete the previous night's backup. I know Access 2007 does not have a built in function to do this like 2010 does, but I have found snipits of code that is said to be able to do this, I just can't put anything working together for my database. Has anyone done anything like that before?

Thank you!

 

[RainLover]

You cannot create a backup from within the database that is to be backed up.

You need to write a separate Database or something in VB. This would be fired by the Task Manager at a time you set.

The should be a Backup tool in the archives. I have a good one that was written by ChrisO.

You could do a search to find it.

 

[davidb88]

RainLover - Thanks for the reply. If this is a shared database though (i.e. saved on a shared drive across our company) and is opened by different users on different machines would I still be able to use the task manager? Or could I create a separate database and save that to the same folder on the shared drive and write VBA in that to backup the main database?

 

[RainLover]

http://www.access-programmers.co.uk/forums/showthread.php?t=233900


This was easy to find.

 

[davidb88]

Hi RainLover- Was there supposed to be something attached to your last post? It just says "This was easy to find". What are you referring to?

 

[RainLover]

RainLover - Thanks for the reply. If this is a shared database though (i.e. saved on a shared drive across our company) and is opened by different users on different machines would I still be able to use the task manager? Or could I create a separate database and save that to the same folder on the shared drive and write VBA in that to backup the main database?

If the Database is on the Server, which is what I believe you said then isn't it backed up along with everything else as part of your maintenance routines at night.

 

[davidb88]

It is backed up along with the rest of the shared drive at whatever intervals the company has set. Management, however, has requested that our own backup be made just in case anything happens to the original, it would be much faster and easier to access our own backup compared through going through the red tape of getting a back up from the company's shared drive back up.

 

[RainLover]

Hi RainLover- Was there supposed to be something attached to your last post? It just says "This was easy to find". What are you referring to?

If you follow the link you will find the Backup tool by ChrisO that I spoke of earlier.

Did you go there and have a look.

 

[RainLover]

It is backed up along with the rest of the shared drive at whatever intervals the company has set. Management, however, has requested that our own backup be made just in case anything happens to the original, it would be much faster and easier to access our own backup compared through going through the red tape of getting a back up from the company's shared drive back up.

That is a good idea. It can take ages to get the IT guys to find something for you.

Again have a look at the link before we go an further. This tool will allow very fast back ups.

I use it when I am designing, where I could do 20 or more backups in one hour without thinking about it.

 

[davidb88]

I am looking at it now. It is a very cool tool, however, I am having a hard time understanding the implementation of such a tool. Would I save this just on the shared drive as well and add the database name that we have to the main screen and then it will auto back it up? Or do I need to copy bits and pieces of the tool's code to implement in my own database?

Thanks for your help!

 

[RainLover]

You only need to back up the back end. Store this very close to what you wish to backup.

Within this Tool write an Autoexec that does the same as the button on the main screen. That is run the backup.

Use Windows Scheduler to fire it.

I would suggest multiple backups at different times.

Say every 2 hours after close of business.

 

[davidb88]

That makes sense. My one remaining question is you mention using Windows Scheduler to fire the auto back up. Would that have to be set up then on every computer that is using the database?

 

[BlueIshDan]

Use a windows scheduled event that starts a batch? have the batch copy your access database to c:\access_backups?

 

[davidb88]

RainLover - In thinking more about it, I could just have the backup program running on my machine to create a backup somewhere on the shared drive, correct? Not everyone's computer would need to be creating a back up. Can you help me out though with the AutoExec script that you reference? What would that look like and where in the tool you linked me to would I place it in?

BlueIshDan - Thanks for your response. I'm not sure what you mean by start a batch. Can you explain if you have a chance? Thanks!

 

[BlueIshDan]

RainLover - In thinking more about it, I could just have the backup program running on my machine to create a backup somewhere on the shared drive, correct? Not everyone's computer would need to be creating a back up. Can you help me out though with the AutoExec script that you reference? What would that look like and where in the tool you linked me to would I place it in?

BlueIshDan - Thanks for your response. I'm not sure what you mean by start a batch. Can you explain if you have a chance? Thanks!

Write a batch file with the xcopy [access_database_location] [access_backup_location] command. Then create a windows event that runs that batch @ the of time(s) you want the backup to happen.

 

[RainLover]

I have not used the file for a while.

All you have to do is open it then hit the set up button where you can choose the file and location.

Next time you open it the back up will be automatically run.

However I have just noticed that it is set up for .mdb extensions. You will have to make adjustments in the code to compensate for it.

Every user has the same front end. Or I would be more correct in saying that except for updates later versions etc the front end stays the same. You would have a master back up for that. It is only the database on the back end that stores all the data. This is what changes constantly and should be backed up regularly.

 

[RainLover]

BlueIshDan - Thanks for your response. I'm not sure what you mean by start a batch. Can you explain if you have a chance? Thanks!

A batch file was originally written in DOS.

It is usually written using a txt file and renaming the extension .bat.

This then becomes an executable file.

Autoexec.bat is a good example. The thing about this file is that it automatically fires when the program starts.

 

[RainLover]

Use a windows scheduled event that starts a batch? have the batch copy your access database to c:\access_backups?

This is a good piece of advise that I overlooked.

Write a little program as a batch file and run that with the use of the Windows Scheduler.

PS I think you have to obey the DOS rules for naming.