Back up database after some interval

[DevAccess]

Hello,

I would like to take backup of perticular database TABLE ( just TABLE ) after specific time every day, can anybody please suggest VBA for this ?

Thanks
DJ

 

[CJ_London]

Your question generates a lot of other questions before it can be answered

what form is the backup to be? excel file? text file?, a table in another db? in the same db?

Is it to be a full copy of the table to replace the previous backup? or do you need multiple backups? Or is it an append to a single backup table to just add what has been added since the last backup?

where do you want to run this from? windows scheduler? the db where the table exists? another db? - if the last two, what is to happen if the db is not open at the time for the code to run?

 

[DevAccess]

The backup database of table has to be Ms access only, one of your question is very good if database is not opened when code has to run from either of the database? As of now let's just think that the code would run from source database A ( A is source access dB from where table would be copied to the acess db B which would replace the old table with new one)

 

[CJ_London]

1 question answered, 2 to go

 

[DevAccess]

I would like to run this from main database , it would be good if you can tell me about how to run from window scheduler as well and Vba from access dB.

 

[CJ_London]

have a linked table for your backup table, then create a new form with he following vba code- something like

 Private sub form_timer()
 currentdb.execute "DELETE * FROM backuptable"
 currentdb.execute "INSERT INTO backuptable SELECT * FROM maintable"
 end sub
  
 private sub form_load()
 me.timerinterval=1000 *3600 'for 1 hour intervals (60 mins * 60 secs * 1000 units per second)
 end sub

note both database and form needs to be open to run

for windows scheduler, you will need access to the server windows scheduler, create a new db with linked tables to both backuptable and main table. Obviously,both db's will need to be on the server.

create a new module and a new public sub called say 'backup' and put the form_timer code in it together with another line

docmd.exit

to close the db once done. Then create an autoexec maco which calls this sub.

save the db.

Then use the server windows scheduler to open the db as required. Reference windows help for how the scheduler works.

 

[DevAccess]

have a linked table for your backup table, then create a new form with he following vba code- something like

 Private sub form_timer()
 currentdb.execute "DELETE * FROM backuptable"
 currentdb.execute "INSERT INTO backuptable SELECT * FROM maintable"
 end sub
  
 private sub form_load()
 me.timerinterval=1000 *3600 'for 1 hour intervals (60 mins * 60 secs * 1000 units per second)
 end sub

note both database and form needs to be open to run

for windows scheduler, you will need access to the server windows scheduler, create a new db with linked tables to both backuptable and main table. Obviously,both db's will need to be on the server.

create a new module and a new public sub called say 'backup' and put the form_timer code in it together with another line

docmd.exit

to close the db once done. Then create an autoexec maco which calls this sub.

save the db.

Then use the server windows scheduler to open the db as required. Reference windows help for how the scheduler works.

Hi about above code, what if I want to run the code at 6PM daily? any way we can take local time of the system ?

 

[DevAccess]

Hi about above code, what if I want to run the code at 6PM daily? any way we can take local time of the system ?

also can you please elaborate on windows scheduler front ? may it would great help if you can tell little about settings and all.

 

[CJ_London]

use the time function

if time=#18:00:00# then....

you will still need set the form timerinterval and timer event however you will need to set your timer interval to run say every second (1000) otherwise it may not run when exactly 6pm so time will never be 6pm at that point. Don't forget the timer event runs in the background so may impact performance.

another way perhaps is

if time>=#18:00:00# and time<=#18:01:00# then....

which widens the window of opportunity for the event to fire, but may result in being fired multiple times.

You will need to experiment



Generally, if you want a specific time, better to use the windows scheduler

 

[CJ_London]

can't help on windows scheduler - it is pretty obvious how it works, just follow the prompts. To find it just look for 'task scheduler'

 

[DevAccess]

Hi CJ,

I happened to work with window scheduler and I am able to open the access db using window scheduler, now would you please explain me your below code little bit with more explanation ?

create a new module and a new public sub called say 'backup' and put the form_timer code in it together with another line

docmd.exit

to close the db once done. Then create an autoexec maco which calls this sub.

save the db.

 

[CJ_London]

pretty much as already described
1. open new db
2. create linked tables to the original table in the main db and the backup table in the backup db
3. create a new module
4. put this code in the new module

 Option Compare Database
 Option Explicit
  
 Public Function Backup()
 currentdb.execute "DELETE * FROM backuptable"
currentdb.execute "INSERT INTO backuptable SELECT * FROM maintable"
docmd.exit
 end function

5. Create a macro called autoexec to run the backup function (or I guess you can use the macro to run the 3 lines of code instead of having the backup function
6. use windows scheduler to run the new db at the appointed time

 

[Lightwave]

As per CJs instructions I would say that you do all of what he says and then you use the task scheduler to periodically run the database procedure. This will create the new file that you want to backup

Basic rundown of Task Scheduler for Windows OS

I would say that after that has been done you will need separate code to actually BACKUP the file. This can be included in the backup script that is triggering CJs code so that after a new database has been created you then backup that copy up. Depending on your requirements you may want to make this into some kind of rolling feature. Whereby you backup per day per week per month and per year etc... Then you will have a set number of files that go all the way back.

Scripting for that will be along the lines of the following adjust as per your requirements.

Using a Visual Basic Scripting to backup databases

 

[DevAccess]

pretty much as already described
1. open new db
2. create linked tables to the original table in the main db and the backup table in the backup db
3. create a new module
4. put this code in the new module

 Option Compare Database
 Option Explicit
  
 Public Function Backup()
 currentdb.execute "DELETE * FROM backuptable"
currentdb.execute "INSERT INTO backuptable SELECT * FROM maintable"
docmd.exit
 end function

5. Create a macro called autoexec to run the backup function (or I guess you can use the macro to run the 3 lines of code instead of having the backup function
6. use windows scheduler to run the new db at the appointed time

CJ,

I have tried to create autoexec macro but it does not allow me to open/run the code it just has predifined functions like opening a form, opening a datasheet.

However I can open a form and on form load I can put the script which you stated above but in that case it is not closing the database after completing required action.

docmd.exit

please advice.

 

[CJ_London]

for the autoexec, use the runcode macro under macro commands

 

[DevAccess]

but it does not allow me to enter code you have pasted, please see attached pic, i am using 2010 access.

sorry I am not sure how to attach picture, but what i can say is there is expression builder after putting your code says builder has invalid repeat expression value.

 

[CJ_London]

the code goes in a new module (not a form module)

for the runcode you would put Backup

 

[CJ_London]

and don't call the module backup, it has to have a different name from the function

 

[DevAccess]

Hi CJ, it worked, but doccmd.exit or docmd.close does not close the database.

 

[CJ_London]

try docmd.quit - my bad