Automating database backup

[tmyers]

I have seen a couple different post/articles on how to do this, but before I just went and paste various code into Access, I wanted to get others opinions on it.
I was reading the following:

Can an Ms Access database create a backup of itself while it's open using vba?

Background: I have a process that links Quickbooks to Ms Access. If a button is pressed, some information will be queried from Quickbooks and then updates Ms Access. If the power goes out, or if th...

stackoverflow.com

How to Create Auto-Backup Database – iAccessWorld.com

justin-hampton.com

It seems like any one of those would work, as all I am wanting to do, is say approximately every hour, the database create a back up version of itself. Ideally, it would save an all new copy of itself with a time stamp. I could then keep however many backups I would want to, or at some point add in where the code would auto-delete all files that are say 5+ days old.

But out of those, which would be the better one to use?

 

[theDBguy]

Hi. I haven't read any of the links you posted, but are you trying to create a backup of the FE or the BE. If the BE, take a look at the sample code I posted a while ago at:

Code Snippets

 

[Gasman]

Here is what I have used, manually though. The functions are because I used the Access Switchboard form.
There is more there than you need for backup, but it is easier to just upload the whole module.

HTH

 

[Isaac]

are you wanting to create a backup version of the file that is being used? the front end? or a separate back end file?

generally speaking, if the answer is the former, that shouldn't be necessary really, because everyone is using their own local copy of the front end, and you don't care if it gets corrupted (etc), they just reinstall by getting a new copy of the master.

but i'll throw this out there just for fun.....recently I was asked to create a backup file of an Excel file - the same one being used. Prior to realizing there was such a thing as SaveCopyAs, I came up with following method which worked a charm and backed up the file every time it was closed:

1) VBScript to copy current file to backed up filename
2) in that VBScript, there was a 10 second wait
3) Shell out the VBScript using vba in the Excel file in its Close event

Technically, you could do something like this with Access too not any different really.

If you are just trying to back up the back end, I would probably create something to be called by windows task scheduler on a server if possible.
Or just double check with your I.T. dept...........I can't tell you how many places I've worked where the people had literally no idea that server backups, available/accessible in the form of Folder Properties>Previous Versions, was already turned on...and there was virtually no need to perform more backups.

 

[bastanu]

I've been using this utility in conjuction with Windows Scheduler to automate\compact Access back-ends:

FBA MS Access back-end compacter - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

It does the compacting on the local C:\ dirve as doing on the network can lead to unusable files if the network connection fails during the compacting.
Cheers,
Vlad

 

[arnelgp]

download and use Goodsync.
it is a Set and Forget backup program.
it also has incremental backup.

 

[Pat Hartman]

If the BE and the master copy of the FE are on a server as they should be, the server should already be getting backed up regularly. You can check with your IT department to find out the frequency. If you think it is insufficient, then look into the utilities at www.FMSINC.com

If you are talking about frequent backups of the FE while you are developing, I can help with some code for that. I do it two ways, one copies the FE to both a local and a server directory and the other exports all objects to text and you can set that up to be wherever you want. In order to not annoy the users, the login form which is the first to open and it stays open throughout the secession and is the last to close only asks "me" if I want to back up or it does it automatically if it is me. This is hardcoded but you can create a table and put the userid in the table so that you can have multiples. That is helpful if you work on multiple computers and have different IDs on each or multiple people might make changes and you want to backup if any developer makes a change.

Let us know why you want the back up and we can give more specific advice.

 

[tmyers]

This would be for the Backend file. It is on our local server, which is backed up nightly. The reason I would like to go a step further would be due to the fact our building is known to drop power randomly several times a year. Depending on the day, a lot of work could potentially be lost if during one of those events, the backend gets corrupted. Granted I don't know the likelihood of such an event since the server has a UPS and such on it. So while the front ends would cut off, the backend would keep chugging along until the server safely shuts down.

 

[Pat Hartman]

Then I suggest the backup utility sold by FMSINC.com

 

[hfsitumo2001]

are you wanting to create a backup version of the file that is being used? the front end? or a separate back end file?

generally speaking, if the answer is the former, that shouldn't be necessary really, because everyone is using their own local copy of the front end, and you don't care if it gets corrupted (etc), they just reinstall by getting a new copy of the master.

but i'll throw this out there just for fun.....recently I was asked to create a backup file of an Excel file - the same one being used. Prior to realizing there was such a thing as SaveCopyAs, I came up with following method which worked a charm and backed up the file every time it was closed:

1) VBScript to copy current file to backed up filename
2) in that VBScript, there was a 10 second wait
3) Shell out the VBScript using vba in the Excel file in its Close event

Technically, you could do something like this with Access too not any different really.

If you are just trying to back up the back end, I would probably create something to be called by windows task scheduler on a server if possible.
Or just double check with your I.T. dept...........I can't tell you how many places I've worked where the people had literally no idea that server backups, available/accessible in the form of Folder Properties>Previous Versions, was already turned on...and there was virtually no need to perform more backups.

Hi Isaac, I am trying to backup just my backend, where tables of the data reside. Do you have any idea for the simple method, I am not so good in MS Access, just learning thru youtube and from you guying in the community.

This is the module that I can call from backup button in the Admin Form, but it has error 429 says : Active X can can not create the file.

Option Compare Database
Public Function SimpleLogin_A_R1() As Boolean
Dim Source As String
Dim Target As String
Dim objFso As Object
Dim Path As String

Source = CurrentDb.Name
'Path = CurrentProject.Path
Path = "C:\TestDB"
Target = Path & "\BackupDB_" & Format(Now(), "mm-dd-yyyy") & ".accdb"

Set objFso = CreateObject("Scripting.FileSystenObject")

If objFso.folderExits(Path) Then
    a = objFso.copyfile(Source, Target, True)
Else
    objFso.Createfolder (Path)
    a = objFso.copyfile(Source, Target, True)
End If
Set objFso = Nothing
Set a = Nothing

End Function

 

[Isaac]

that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.

 

[hfsitumo2001]

that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.

Thank you Isaac, you are awesome... it works like a chant, after I also change objFso should have been objFSO, upper case for FSO. Ok this is my trial, now I want to learn how to make an automatic back up at mid night, to back up the back end only, because the data is there. Again thank you for your help, because actually I just followed what is explained on the Youtube

 

[hfsitumo2001]

that's because you have an misspelling in FileSystenObject, n instead of m

what you want to do is extremely trivial, the copying of a file. so should be OK. except you may not be able to copy the backend during such time as anyone is actively linked to it, actually on this point i can't remember, due to Previous Versions & sql server it's been a long time since i needed to back up access data.

Isaac, I do not understand, on my trial database it works, but on my actual database it did not work with the compile error. Could you help where am I wrong?
Below is the picture:

 

[Isaac]

is that thing you are calling the same as the name of a Module?

 

[hfsitumo2001]

is that thing you are calling the same as the name of a Module?

Yes Isaac, but please ignore my question, because the problem is I thought the name of of the function should be the name of the database, even that is that makes it wrong. I misunderstood the sample given in the youtube.

If you have the sample of the module that can automatically save tables in server at midnight. Thank you.

 

[theDBguy]

If you have the sample of the module that can automatically save tables in server at midnight. Thank you.

You shouldn't need a module. The Task Scheduler should be able to do the job for you. Just a thought...

 

[Isaac]

Yes Isaac, but please ignore my question, because the problem is I thought the name of of the function should be the name of the database, even that is that makes it wrong. I misunderstood the sample given in the youtube.

If you have the sample of the module that can automatically save tables in server at midnight. Thank you.

What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub

 

[theDBguy]

What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub

Although I haven't tested it, I was wondering if a simple xcopy command will do without having to create a batch or script file. Just thinking out loud...

 

[hfsitumo2001]

What I would do personally is write a VBScript to do the file copy (or do it in Access if you are more comfortable there), then create a Scheduled Task to run at midnight and execute the VBScript, or, run the Access sub

Could you give me your sample of VBScript Isaac?

 

[hfsitumo2001]

Although I haven't tested it, I was wondering if a simple xcopy command will do without having to create a batch or script file. Just a thinking out loud...

OK DBGuy, I will look into it. 14 years ago I think you helped me on making Church membership database back up, but let me try to dig where is it in my laptop.