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!

 

[legalhustler]

Are you familiar with scheduling a VBS through the Windows Task Scheduler?
Schedule a task - Windows Help


Here's how I normally do this :

1) Take ranman256 function and put that into your Access DB Module.
2) Copy and paste this below into NotePad - make sure to change the variable the location of your DB path and Function name

 Dim db
Dim strDbName
dim strMacro
strMacro = "Your Function Here"
strdbName = "Your Path Here for DB"
Set acc = createobject("Access.Application")
 
'this temporarily changes the macros security to low to avoid messages.
'only works with access 2000 onwards
if acc.syscmd(7) >= 10 Then
acc.AutomationSecurity = 1
End if
 
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
acc.run strMacro
acc.quit

3) Save that Notepad file as something but make sure to add the extension .VBS (ie. Test.VBS)
4) Test to see if your script file works by double clicking it. It should run that line of code in the script file to open up access, run your function, and then close out the database.
If this doesn't work make sure that the function or code in Access is correct or post what message you get.

5) Create a Task in Windows Task Scheduler
6) For Trigger- make sure your trigger is set based on the time that you want and the options to repeat etc.(should be self explanatory)
7) For Actions- make sure you search for the name of the VBS file and attach it to the Task.
edit anything else that you need for conditions or what not.

I do this all the time but I'm not here at 6PM EST but you could technically leave your computer logged on or sleep and check on the options for the conditions. You could even have your computer shutdown after it runs this too.
Note the script stuff won't run if your computer is shut off - in which you set up the Task scheduler.

The other thing I've done in the past is get IT's help to which they just take your VBS file and add it to run along with theirs over night. (you'll need buy in on this though)

Hope that helps you.

My script VBS file opens my database, but it does not run the function nor close out the database. After it opens the database the following error message shows:

Windows Script Host
Microsoft cannot find the procedure 'BackupDB.'
Code: 800A09D5

Here is the function that I put in my Access module:

Public Function BackupDB()
Dim vSrc, vTarg
vSrc = "[URL="file://\\folder\myDB.accdb"]\\folder\myDB.accdb[/URL]"
vTarg = "[URL="file://\\folder2\bak\myDB"]\\folder2\bak\myDB[/URL]" & Format(Date, "yyyymmdd-hhnnss") & ".accdb"
FileCopy vSrc, vTarg

Here is my VBS script file code:

Dim db
Dim strDbName
dim strMacro
strMacro = "BackupDB"
strdbName = "C:\Users\joe.smith\Desktop\BackupDB.accdb"
Set acc = createobject("Access.Application")

'this temporarily changes the macros security to low to avoid messages.
'only works with access 2000 onwards
if acc.syscmd(7) >= 10 Then
acc.AutomationSecurity = 1
End if

Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
acc.run strMacro
acc.quit

Please advise. Thanks!

 

[TerryHogarth21]

Seems you are missing

 End Function

in your function.

If you do this, are you able to run that function in Access by itself?

If that works, then try the Task Scheduler again.

 

[legalhustler]

Seems you are missing

 End Function

in your function.

If you do this, are you able to run that function in Access by itself?

If that works, then try the Task Scheduler again.

I revised the Access function to:

Public Function BackupDB()
Dim vSrc, vTarg
vSrc = "C:\Users\joe.smith\Desktop\Database\BackupDB.accdb"
vTarg = "C:\Users\joe.smith\Desktop\Database\CopyofBackupDB" & Format(Date, "yyyymmdd-hhnnss") & ".accdb"

FileCopy vSrc, vTarg
End Function

Note for VTarg variable I don't have anything in that folder. I suppose that is where the backup of the DB will go with the date stamp and .accdb extension.

I tried to run the function in Access but I get runtime error. It wants me to debug the last line of the above function (the variables):

FileCopy vSrc, vTarg

 

[TerryHogarth21]

Is your location correct?

If you go to File in the Access DB - in the info it should have the location.
I thought it would have used your unix login like the below

C:\Users\jsmith\Desktop\Database\

 

[legalhustler]

Is your location correct?

If you go to File in the Access DB - in the info it should have the location.
I thought it would have used your unix login like the below

C:\Users\jsmith\Desktop\Database\

Path looks correct and is the same as under Info:

C:\Users\joe.smith\Desktop\Database\BackupDB.accdb


I'm not sure why it cannot find the procedure 'BackupDB.'

 

[TerryHogarth21]

When you hover over the that final line in debug mode (F8) what does it display?

What's the error message number?

Also you need to get into the habit of also not being ambiguous in your naming.

Your Function name and the database name in one of your variables is the same "BackupDB"

Try changing that to see if it works too.

 

[legalhustler]

When you hover over the that final line in debug mode (F8) what does it display?

What's the error message number?

Also you need to get into the habit of also not being ambiguous in your naming.

Your Function name and the database name in one of your variables is the same "BackupDB"

Try changing that to see if it works too.

I change the naming convention:

Public Function BackupDB()
Dim vSrc, vTarg
vSrc = "C:\Users\joe.smith\Desktop\MasterDB\MyDB.accdb"
vTarg = "C:\Users\joe.smith\Desktop\MasterDB\MyDBBackUp" & Format(Date, "yyyymmdd-hhnnss") & ".accdb"
FileCopy vSrc, vTarg
End Function

WHen I hover over the last line I see the following C:\Users\joe.smith\Desktop\MasterDB\MyDBBackUp20140917-000000.accdb

I get Runtime error '70'

Permission denied

 

[TerryHogarth21]

I change the naming convention:

Public Function BackupDB()
Dim vSrc, vTarg
vSrc = "C:\Users\joe.smith\Desktop\MasterDB\MyDB.accdb"
vTarg = "C:\Users\joe.smith\Desktop\MasterDB\MyDBBackUp" & Format(Date, "yyyymmdd-hhnnss") & ".accdb"
FileCopy vSrc, vTarg
End Function

I get Runtime error '70'

Permission denied

Yep I expected that as well. You are in the DB as it is trying to make a copy of the DB when running that line of code.

Now save the DB and run the script to see if it works.

 

[TerryHogarth21]

I would have also thought the user would have been joe.smith (unless that was just made up).

do users have permissions in that folder to write/read etc to the DB? (ask the network admin)

 

[legalhustler]

I would have also thought the user would have been joe.smith (unless that was just made up).

do users have permissions in that folder to write/read etc to the DB? (ask the network admin)

joe.smith is fictitious. The folder is on my local desktop with my username. This is just for testing purposes. Once everything is fixed I will put it in the network folder with Production data. I will most likely have to talk to network admin to grant folder privileges. Will run script tomorrow when I go to work. Thanks.