Automatically Backing Up An Access Database at a Particular Time

[legalhustler]

I have an Access database that I would like to back up automatically either on the network drive (or an online storage place would be best...dropbox?). My database has tables, queries, forms, reports, macros, etc so the ENTIRE database needs to be backed up. I would like to backup of the database to occur at a specific time, say 1AM every day, or once a week, say Fridays at 1AM.

This is a multi-user database so before the back up runs, it will need to check if there are any users that has the database open, and if there are then it should automatically boot the user after 5 mins and then run the back up.

I've heard VBA or running some batch file will do the job. I have very little experience in both.


TIA!

 

[TerryHogarth21]

I'm going to also try your method as well.

In the below, what am I supposed to put for "Your Function Here"?

strMacro = "Your Function Here"

Also, do we need to put " " for both the lines after we change the info?

Thanks!

Whatever the name of the function or subroutine in Access that you want to run.

In your case it looks to be

 strMacro = "BackUp"

You need the quotations so don't delete that out for the name of the Function/Sub or the Location of the DB.

I do have a question though in regards to the BackUp code - does that only run the back up at a certain time based on the input?

If that is the case if you go through the VBS route you don't need that line to ask for the time to backup since the Windows Task Scheduler will run it at that time.

 

[legalhustler]

Whatever the name of the function or subroutine in Access that you want to run.

In your case it looks to be

 strMacro = "BackUp"

You need the quotations so don't delete that out for the name of the Function/Sub or the Location of the DB.

I do have a question though in regards to the BackUp code - does that only run the back up at a certain time based on the input?

If that is the case if you go through the VBS route you don't need that line to ask for the time to backup since the Windows Task Scheduler will run it at that time.

Thanks.

If you look at the code closely, you can see that the default time is set to 6:00PM. You can change it accordingly to your desire. When you open your DB a screen will prompt with a textbox that says 6:00PM, but you can manually change the time to whatever you like on there too. Then you can either click Ok or Cancel.

dTime = InputBox("Create a backup at", , TimeValue("6:00PM"))

My question is how do I change the code I posted so that it's given a generic name such as "Procurement Database" so that I don't have multiple backup copies in my folder with different dates? So when I backup my DB it overwrites the previous verision in my folder, thus maintaining only one backup copy. Then I guess I can use the folder view to see the timestamp of the latest backup.

 

[TerryHogarth21]

Think it is on this line for the name of the file.

    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".mdb"

This looks like it's adding a Date in the m-d-yy format after the current project name so I think it would look like "Procurement Database-9-11-14".

You would have to replace it to look something like this

sFile = "C:\Procurement-Database" & ".mdb"

What version of Access do you have? I thought everything should be saved as .accdb extension with 2007 access and up?

 

[legalhustler]

Think it is on this line for the name of the file.

    sFile = CurrentProject.Path & "\" & Format(Date, "m-d-yy") & ".mdb"

This looks like it's adding a Date in the m-d-yy format after the current project name so I think it would look like "Procurement Database-9-11-14".

You would have to replace it to look something like this

sFile = "C:\Procurement-Database" & ".mdb"

What version of Access do you have? I thought everything should be saved as .accdb extension with 2007 access and up?

I have Access 2010. Yeah, you're right. I need to change the extension to .accdb instead of .mdb in the code you provided aboved even though it still copied the DB with a .mdb extension.

So if I have a backup file already that is "C:\Procurement-Database" & ".accdb" then create another backup it'll replace the previous version automatically?

 

[TerryHogarth21]

I have Access 2010. Yeah, you're right. I need to change the extension to .accdb instead of .mdb in the code you provided aboved even though it still copied the DB with a .mdb extension.

So if I have a backup file already that is "C:\Procurement-Database" & ".accdb" then create another backup it'll replace the previous version automatically?

Yea that should work, just try it out- the only thing I think will happen is that you may get a warning that a file with that name exists, but you could easily turn off the warnings in part of the access code to bypass it.

 

[xenou]

I also created a solution for this if anyone is interested:
MCCompact_DB

Although you can actually get backups in maybe one or two lines of code using scheduled tasks, my solution is made to generically provide a lot of other features, notably:
* compact and repair
* backup rotation with datestamped filenames and cleanup of expired backups
* handles many databases, each with it's own parameters such as # of backups or backup location

I basically have my "utility" database where i keep the code. The scheduled task opens the utility database and runs the backup routine, which then kicks off everything else. However, a disadvantage, compared to other solutions as suggested above, is that you cannot compact/repair unless everyone is out. So it's probably not suitable for databases that have multiple users and is frequently open (or left open, at any rate, since you'd run this in the middle of the night, of course). I can't remember - however, possibly I have a "no compact" option so it might still be possible to backup only (I should look into that! Might be a good fallback to backup even if the compact can't run).

 

[legalhustler]

I also created a solution for this if anyone is interested:
MCCompact_DB

Although you can actually get backups in maybe one or two lines of code using scheduled tasks, my solution is made to generically provide a lot of other features, notably:
* compact and repair
* backup rotation with datestamped filenames and cleanup of expired backups
* handles many databases, each with it's own parameters such as # of backups or backup location

I basically have my "utility" database where i keep the code. The scheduled task opens the utility database and runs the backup routine, which then kicks off everything else. However, a disadvantage, compared to other solutions as suggested above, is that you cannot compact/repair unless everyone is out. So it's probably not suitable for databases that have multiple users and is frequently open (or left open, at any rate, since you'd run this in the middle of the night, of course). I can't remember - however, possibly I have a "no compact" option so it might still be possible to backup only (I should look into that! Might be a good fallback to backup even if the compact can't run).

Your site is helpful. May I suggest giving a little better explanation for beginners. For instance, "Sending Email from Access or Excel using CDO", where am I supposed to saved each of those Subs and Function? Put in a Notepad and change the extension to .vbs? It will be also helpful if you could list a reason when you would use them (Some bullet points would be great).

 

[xenou]

Hi,
there are some good sites for beginners:
Excel VBA Basic Tutorial 1
VBA Tips: Writing Your First VBA Function

My site really assumes you already know the basics of Excel VBA programming (By the way I would recommend you read Excel VBA Programming for Dummies by Walkenbach as the simplest way to get up to speed - it covers everything you need to know simply and clearly).

 

[legalhustler]

Hi,
there are some good sites for beginners:
Excel VBA Basic Tutorial 1
VBA Tips: Writing Your First VBA Function

My site really assumes you already know the basics of Excel VBA programming (By the way I would recommend you read Excel VBA Programming for Dummies by Walkenbach as the simplest way to get up to speed - it covers everything you need to know simply and clearly).

Thanks, I will look into those. I know some basics, but I was just trying to understand the directions on your site, because it doesn't sound clear as far as where I am supposed to save the Sub/Function for some of your other topics. It would be nice if you could give examples of when you would use the different things you created. Maybe I'm being a little picky, but Allen Browne does the best job of explaining things IMHO . Take for instance the following:

http://allenbrowne.com/casu-08.html

 

[legalhustler]

**delete***