How to set up an automatic backup?

[stevekos07]

I know this question has probably been asked a thousand times, so if you have a good link to point me to that will be great.

I have a macro that runs on close to remind the user to perform a backup. The backup is a simple append query that saves a table recording the day's client service records.

Obviously because it relies on the user responding correctly, there are inevitable errors, either with duplicate backups, or worse, backups being missed.

I would like to be able to run the macro automatically on a schedule. Can anyone help me with this?

Edit: Another factor which needs to be accounted for is that this backup needs to happen only once regardless of how many users are using the database. The database is split with each user having their own full copy of the front end objects. I don't want each user's version running the backup on top of others, so there will need to be some kind of check to see whether there is already a record with the current date in the backup table before allowing the macro to run.

I do have a front end version which is a developer-only version, so maybe it would be best to have the scrip resident in that version, but it may not be open at the time the backup needs to occur, so a script to run the backup from this version without needing to open it would be necessary. Ideas on other approaches would be appreciated, but please note I am not very experienced in VBA (but happy to learn).

I'm getting lost just explaining this to you!!!

 

[Ranman256]

Make a separate db with a form , set the timer to 1000. (1 second)
In a box on the form,txtStart, set the default time.
In the OnTimer event, check the box.

 If format(now(),"hh:nn") = txtStart then 
     vTime = format(now(),"yyyymmdd-hhnn")
      VTarget ="c:\backup1\" & vTime & "sourcefile.mdb"
     Filecopy "c:\folder\sourcefile.mdb" vTarget
     Docmd.close
End if

 

[stevekos07]

Make a separate db with a form , set the timer to 1000. (1 second)
In a box on the form,txtStart, set the default time.
In the OnTimer event, check the box.

 If format(now(),"hh:nn") = txtStart then 
     vTime = format(now(),"yyyymmdd-hhnn")
      VTarget ="c:\backup1\" & vTime & "sourcefile.mdb"
     Filecopy "c:\folder\sourcefile.mdb" vTarget
     Docmd.close
End if

Ok Ranman256, I believe you but I have no idea what you just said .

In English:

• I set up a separate database - ok, got that.
• In that database I have a single form on which I place a single text box - in that box I set the default time. - I guess that's the time I want the backup to run, correct??
• I "set the timer to 1000" - how, ???
• In the "On Timer" event I "check the box" - What????
• from there I'm totally lost. I built my database with very little VBA code and am not really comfortable working with it yet so you might have to go into some detail explaining the code you wrote there.

 

[arnelgp]

another approach, without coding is to download syncovery or goodsync software (available at peer-to-peer).

 

[CJ_London]

you can also look at using the windows scheduler

http://www.digitalcitizen.life/how-create-task-basic-task-wizard

 

[stevekos07]

another approach, without coding is to download syncovery or goodsync software (available at peer-to-peer).

arnelgp, my problem is not the backup of the database as such. The database is backed up on our servers automatically.

I use a macro to run an apend query. This query simply apends one table to another existing table as a backup. The first table's data changes every day and throughout the day, so we need a backup of the table at a given point in time to use for statistical reporting.

I need to set up some kind of time activated macro that will run the query at a set time, and only have that macro on a copy of the database front end that is not used by a human operator. This would ideally be opened by Task Scheduler at a pre-determined time.

Having the "dormant" copy then open and run an autoexec macro would then call the macro in the other database to run the apend query. My issue now is how to compose the autoexec macro to do that.

 

[Ranman256]

The form has a property TIMER INTERVAL
its in millisecs so set to 1000 (1 SECOND)

the ONTIMER is the event that runs base on timer.
so it will run every second.

 

[CJ_London]

further to my post - take Ranmans advice and have a separate db with just your macro in it, renamed as autoexec and links to the appropriate tables then set up windows scheduler on a relevant server to open the file at a specified time

 

[apr pillai]

I think the following link will help you in designing and setting up your daily backup procedure: Database Daily Backup

 

[arnelgp]

sorry have over-read your post.

you need additional field in your backup table, which corresponds to the autonumber field in the table you want to backup. create an indexed on this field (without duplicate).

in that way it is easy to be traced (traceability).

with this method you only perform incremental backup. and no possibility of duplication.

 

[stevekos07]

sorry have over-read your post.

you need additional field in your backup table, which corresponds to the autonumber field in the table you want to backup. create an indexed on this field (without duplicate).

in that way it is easy to be traced (traceability).

with this method you only perform incremental backup. and no possibility of duplication.

Thanks for everyone's advice. What I think I have worked out that I need to do is:

1. Add another field to the primary key on the backup table. This is because the source table has ClientID as the primary key, but the amend table has many records for every ClientID.

So, I need to know how to add another field to the primary key. the CallDate field is the most logical as there should be no duplicate combinations of ClientID and CallDate in the table.

2. Once this is in place I need to set up a way of triggering a macro that triggers the append query, obviously a timer related event. This could be triggered on any open version of the database, and with the revised primary key this will prevent any possibility of duplications. The timer can be set for the same time every day.

I need to know how to do this.

3. The append query "backup" can then run in the background safely with no possibility of being missed or of being duplicated.

Please bear in mind that if you are going to recommend a VBA solution that I have some understanding of VBA and am happy to learn how to write it but don't assume too much knowledge on my part.

Cheers, Steve.

 

[Galaxiom]

1. Select both fields in design view and click the Primary Key button.

2. I would not use the timer as it has big overheads. Use the Windows scheduler to open the dedicated backup database with an AutoExec macro.

3. Use an Insert query with an outer join from the source data to the destination table and a Null criteria on the key fields on destination side of the join. This query will only insert records that don't already exist.

Personally if I was that worried about backups I would put the back end on SQL server and back up the whole database. Server backups can take snapshots while users are logged in. It can even roll back transaction by transaction with the right settings.

 

[stevekos07]

1. Select both fields in design view and click the Primary Key button.

2. I would not use the timer as it has big overheads. Use the Windows scheduler to open the dedicated backup database with an AutoExec macro.

3. Use an Insert query with an outer join from the source data to the destination table and a Null criteria on the key fields on destination side of the join. This query will only insert records that don't already exist.

Personally if I was that worried about backups I would put the back end on SQL server and back up the whole database. Server backups can take snapshots while users are logged in. It can even roll back transaction by transaction with the right settings.

Thanks Calaxiom

1. Yes that seems obvious but I didn't know if there was any problem with adding a field to the PK in an existing table. I will try that today.

2. My only issue with this is whether the database can open with no users logged in. If it can then no problems, I can schedule it for any time. If not I will need to talk about how to make this work with our IT people.

3. You might need to explain this a little more. I know what an insert query is and I know what an outer join is but the Null criteria on the PK fields? I am not quite sure what you mean.

 

[stevekos07]

Actually Galaxiom I would have thought that the standard update query I have now would suffice, as the new dual-field PK will fix any issues with duplicates, so only new records will be inserted anyway.

That simply leaves the scheduling issue and actually writing the Macro to run the update behind the scenes with no user input.

 

[Galaxiom]

Actually Galaxiom I would have thought that the standard update query I have now would suffice, as the new dual-field PK will fix any issues with duplicates,

The query would throw the "x rows could not be inserted due to Primary Key conflict" warning.

You can supress this if you are using DoCmd.RunSQL or leave out the dbFailOnError in CurrentDb.Execute but then you won't get told about any other kinds of problems.

 

[stevekos07]

The query would throw the "x rows could not be inserted due to Primary Key conflict" warning.

You can supress this if you are using DoCmd.RunSQL or leave out the dbFailOnError in CurrentDb.Execute but then you won't get told about any other kinds of problems.

Yes you are right. I ran the macro and got the error message. I am happy that I got the error because it confirms that the PK is preventing duplicates. What I want now is to be able to run the macro and only have an error for issues that are not related to the PK conflict.

What I want is for the macro to run on quitting Access. Currently I activate the "backup" macro from a hidden form with an On Close even, which works fine.

If the append query called by the Macro has already been run and would cause a conflict I want it to simply bypass that error, and go to another action which is unrelated to this process, and which requires user input.

How do I write an OnError action in the macro to handle this? What I have currently is a message box at the end of the current procedure to indicate that the backup has been completed successfully. Perhaps this can be called on exit to indicate to the user that the backup has been successfully completed before they proceed with other end of day actions.

 

[stevekos07]

Ok, I'm still thinking. What I am considering is including an IF expression at the beginning of the "backup" macro to check to see if there are any records in the target table with today's date. If not, proceed to run the append query, if so, go to the next action.

Does that make sense? If so, how would I write the expression in the IF action?

 

[stevekos07]

Ok. This is what I came up with as a macro to do the job. It works, and importantly allows the option for any user who exits the database before the backup is done to opt out. (This is not a true backup of the database but simply appends a "snap shot" of the clients table to a "Daily Stats Backup" table. System backups happen automatically every couple of hours and are adequate for our needs).

This is important because if the user has to close down the database for any reason other than finishing up for the day they can delay the backup until then. This means that if there is some system error that necessitates closing out of the application we don't end up with a premature backup that has to be edited out of the backup table.

However once the backup has been done by any user the macro will stop at the initial check of the table.

I think this is a cool solution if I say so myself

<ConditionalBlock><If><Condition>DLookUp("[CallDate]","[tblDailyStatsBackup]","[CallDate]= Date()")</Condition><Statements><Action Name="StopMacro"/></Statements></If><ElseIf><Condition>MsgBox("You need to backup today's call stats. Proceed?",4)=6</Condition><Statements><Action Name="OpenQuery"><Argument Name="QueryName">qryDailyCallsBackup</Argument></Action></Statements></ElseIf><Else><Statements><Action Name="StopMacro"/></Statements></Else></ConditionalBlock><Action Name="MessageBox"><Argument Name="Message">Daily Stats Backup Complete!</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>