Create Backup_Automatically

[MUKESHY12390]

Hello Frnds,

Is there any way to create Backup on daily basis automatically for Access database.

both way VBA with Non VBA .

 

[Micron]

I assume you mean a copy of the db on a drive.
Non vb - use Task Scheduler to run a command prompt that produces a copy. Each time it runs, the latest state can be copied over this backup file.
vb - I've never created a db to copy some other db if that's what you mean. It's far easier and less work to do the aforementioned. If you're asking about updating a db by pulling in new or revised records, that's a different animal altogether. NOTE the following is for guidance only and you should be careful about how you modify it for your own use on network or local drives. The way it is written, it has to be in the same folder as the db being copied. It was called by TS, but also as part of the db updating routine. If it didn't run in the middle of the night for some reason, this would run as part of a user driven update process. One thing the code has that took me a lot of research to find is a way to deal with the fact that not everyone in the user group had the same letter alias for their network drive. The old version would fail if I coded for F: and the user's drive label was M: for example. So this is for a command line file (.bat) - it's not vb code. It will add _bak to the name of the db being copied. You'd have to change the Access file extension to suit your system (e.g. accdb or whatever).

rem to copy YourDb to backup database
rem must be run from this folder

cls
@echo off

rem next line sets directory to current location, but using their assigned drive letter (e.g. F, L, etc.)
set MyDir=%~dp0

rem now use that value by reference
cd %MyDir%

:CopyFiles
copy %MyDir%YourDb.mdb %MyDir%YourDb_bak.mdb /y

:quit

 

[MUKESHY12390]

thanks Micron.

As I have never used this Dos programming (just wild hunch). so can you send me link which help me to understand more about it.

Before I go for this , Just wanna be ready for any bug comes in future or any changes require .

 

[Micron]

Because it can be referred to as a batch file, bat file, command line prompt, CLI (command line interpreter) you should research at least one of these terms. You will probably find the results contain two or more of the terms I just listed. Here's one that will get you started, and covers what I failed to mention - that all you need is NotePad.
How To Write A Simple Batch (.bat) File

 

[MUKESHY12390]

thanks ...

apart from this is there any way to "Create RealTime Backup".?

 

[Joe4]

All a "Real Time Backup" really is, is just a copy of your database.
As Micron pointed out, there are various different ways to copy a file. He gave you some options, but there are others.
Some scheduling somewhere programs may even have built-in functions that will do that for you.
I often use the method Micron proposed, run a very simply batch file to copy the file via a scheduling program like Windows Scheduler.

 

[MUKESHY12390]

I'm currently using this, But the problem with this code is every time it copy entire database .whereas I am looking like for eg. file size is 500 MB and after 5 minutes it went to 550 MB. so it should add extra 50 to backup not whole file again and again.

Sub creatingBackup()
Dim Source As String
    Dim Target As String
    Dim retval As Integer
    
    Source = CurrentDb.Name
    Target = Source
    Target = "\\mks\path" & "\"
    Target = Target & CurrentProject.Name & " " & Format(Date, "mm-dd-yyyy") 
    Target = Target & Format(Time, "hh-mm-ss") & ".accdb"

If VBA.Dir(Target) <> "" Then
      'Debug.Print "Backup is already created"
    ' create the backup
     ' Call useShellPopUp
Else
      retval = 0
      Dim objFSO As FileSystemObject
      Set objFSO = New Scripting.FileSystemObject       '  CreateObject("Scripting.FileSystemObject")
      objFSO.CopyFile Source, Target, True
      Set objFSO = Nothing
End If

End Sub

 

[Joe4]

I'm currently using this, But the problem with this code is every time it copy entire database .whereas I am looking like for eg. file size is 500 MB and after 5 minutes it went to 550 MB. so it should add extra 50 to backup not whole file again and again.

While I know that you can do incremental or differential backups on a SQL database, I don't know if you can do that with Access. I have never seen or heard of anyone doing this, and I tend to think that the structure of Access may not lend itself to being able to do this.

 

[xenou]

Also if your file is growing 50MB every 5 minutes it will crash very soon simply by reaching maximum file size! There is such a thing as replication in Access but I've never heard of it actually being used. Most times if you need that level of adminstrative support you'd upgrade to sql server. Even something as simple as real-time backups in Access are complicated by the fact that you cannot copy the file while it is in use.

 

[MUKESHY12390]

thanks Joe and Xenou

if your file is growing 50MB every 5 minutes

this was just an example. What I was trying to explain -there would be some new data adding in every 5 minutes may be just one record.