Saving Access File Every Hour 5

[mspeppa19]

I have an access database that is used to keep track of incidents. There are about three programs total in the whole thing, and their all seperate MS Access databases. What I wanted to do is code it somehow so that they save in a totally seperate directory every hour. The reason I want to do this is although back-ups are done everynight, I'm worried if the programs ever do crash lets say in the middle of the day, that they will still lose those incidents that were entered that day (they could lose up to 10 incidents and they are really long and tedious to enter) so if there is someway to back-it up every hour so that the back-up directory just keeps on writing over itself than losing one hour of data is better than losing 5 or 6. Thanks in advance

 

[FancyPrairie]

You could take advantage of the Windows task scheduler. Create a DOS command procedure that copies your database from 1 directory to another. Then have the Windows task scheduler run the batch job every hour. To check out the task scheduler, goto Start|Programs|Accessories|System Tools|Scheduled Tasks.

 

[zevw]

There is many ways how to backup a Access Database:

I had the same issue in my company and in the begining I lost sometimes a days worth of data 70 to 80 records. Well I flipped out.

I use to run an On Timer Event Procedure and do a FileCopy to a different directory. This was not very smart because if 1 user logged in 15 minutes after the other it would copy the Database every 15 minutes. It also slowed down the system.

I eventually created a batch file that runs every 1/2 Hour and copies the most recent Database file to a different directory and the backup before to another name up to 4
copies like this:

copy C:\Backup\{DatabaseName}2.mdb C:\Backup\{DatabaseName}3.mdb
copy C:\Backup\{DatabaseName}1.mdb C:\Backup\{DatabaseName}2.mdb
copy C:\Backup\{DatabaseName}.mdb C:\Backup\{DatabaseName}1.mdb
copy C:\{Directory}\{DabaseName}.mdb C:\Backup\{DatabaseName}.mdb

The reason I have 4 copies is because the Tables can sometimes be corrupt and the users won't notice till a while later more than a half hour.

Schedule it in Task Scheduler to run every 30 minutes
I hope this helps;

 

[psemianonymous]

I use this batch file to back up my current development copy, change it to suit your needs. I got the date/time environment variable code from some batch file web site.

I don't know how bad your corruption issues are, but so far I've lost only two records; one memo field per corruption incident. It may not be as bad as you think...

for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set month=%%b& set day=%%c& set year=%%d)
for /f "tokens=1-2 delims=/: " %%a in ('time /t') do (set hour=%%a& set minute=%%b)
c:
cd\atemp\dev
mkdir G:\Access\Backup\backup-%year%-%month%-%day%-%hour%-%minute%-development
xcopy *.md? G:\Access\Backup\backup-%year%-%month%-%day%-%hour%-%minute%-development\*.* /s

 

[JeremyNYC]

foolio,

Do you happen to have the URL for that batch file page? I've seen a few, and one was good, but I no longer have the URL.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[psemianonymous]

I honestly don't remember where I got the above "date /t" parser, and Google-ing turned up a whole lot of hits I didn't recognize.

Something I don't remember, but is in my Favorites:

http://www.ericphelps.com/batch/

Something else I don't remember (but seem to remember a little) is this German (.de) web site that has a lot of batch file stuff:

http://www.fpschultze.de/b33.htm

(that's specifically the date/time batch file, link to main page is there)


Those are pretty nice sites. I don't use batch files much, so I don't have any sites that I've used time and again or anything I've really looked into...but there are the links anyway.

 

[mp9]

As an alternative, and if you have Winzip, you can use the approach described at

http://www22.brinkster.com/accessory/general/002.shtml

to take zipped backup copies of your db and all related files. This approach uses a combination of batch files, Winzip and Winzip's free-to-download command line interface plug-in. I use it twice a day, every day for six different multi-user databases and have yet to suffer any problems.

Hope this helps.

 

[pduncan]

What if the mdb file is open when you attempt to copy it as a backup? If you copy it while you have an active ibd lock, will the mdb file still be considered to be a "good" copy?

PDUNCAN - MEMPHIS, TN

“Friends help you hide. Real friends help you hide bodies.”

 

[JasonPurdueEE]

I have the same question as pduncan. I have a batch file backup utility run nightly to do backups and I thought that everybody had to be out of the db for a good copy to be saved. I added the following line to my batch file to make sure everybody was out.

if not exist "\\UNC\*.ldb"

but if this is not needed, I can take it out so that the backup utility will always execute.

JASON

______________________________
Sleep is a poor substitute for coffee.

 

[pduncan]

Jason - does your 'if not exist "\\UNC\*.ldb"' work OK for you? I never thought about doing that.

PDUNCAN - MEMPHIS, TN

“Friends help you hide. Real friends help you hide bodies.”

 

[JasonPurdueEE]

Hi pduncan,
Yes it works very well.

______________________________
Sleep is a poor substitute for coffee.

 

[JeremyNYC]

There's been a good bit of discussion about this. i don't know where the general consensus ended up. Check the google archive of comp.databases.ms-access. I know david fenton was in on the discussion I remember.

What I do know is that I make copies of opened back ends every day, and I check them regularly, and I have never once had a problem. This does not, however, mean that you won't have a problem, or that I would try to refute other people reporting problems. Just that I've never had one.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JasonPurdueEE]

so nobody knows for sure which way is the *best* way to do it? I think for now I'm going to stick with waiting for everybody to be out before backing up. my backup utility runs at night when everybody should be out anyway so there shouldnt be any problems. that is, provided a user doesnt leave it open and go home for the night. if this occurs, it doesnt perform the backup and is logged in my backup log file which I view in the morning. now to come up with a way for my batch file to email me on such event so I dont have to read the log every morning...

______________________________
Sleep is a poor substitute for coffee.

 

[smitan]

Hi JasonPurdueEE, I also had this BackUp problem with an open db. I made a timer thta closes the db before the backup is run.

Have a nice summer,
Hans

 

[epoirier]

Backup the database when is open by user can be corrupt the database.

Me i suggest to you is backup the data only a been changed or added.

You can add in each table a field daterecord. This field is modify when user add or modify a record and take the system date.

You can made add query for each table to fill other database (backup). In add query you need to select only the record with the datesystem - 1 day an execute this backup after 12h00 AM.

 

[ALEKSJAY]

Here are my two cents. Another way could be to have a table in another MDB with the same fields (and a timestamp) and link it to the Active database. Then have some code that updates at regular intervals this other table. Or something like that.

 

[psemianonymous]

One reason to make multiple backups by copying the file is so that you can do a clean overwrite-copy of the backup in case of disaster. I'm not fond of the idea of hourly "update table" routines because they would have to encompass EVERY change made to the production table. So if a record was deleted, you would have to delete the record from the main table, but if the record's primary key changed instead, you would have to be able to figure that out (by the way, it's VERY difficult to identify the above two occurrences).

I suppose you could use Replication to do updates to your backup copy and that would fix all the synchronicity problems. But what if someone deletes all the items in one of your tables and your backup copy does the same 30 minutes later? It becomes useless, because it has the same bad information as your original. THIS is why you have multiple backups.

 

[AvGuy]

Why don't you just use the timer function to write a backup database every hour? Create a function that kills then creates a new database in a separate folder and then use MakeTable queries to copy the data in the current tables over to the backup database. Set the timer to run the code every hour. Seems rather simple to me.

AvGuy

 

[MichaelRed]

The above uniformly fail to mention the process of simply creating an audit trail. There are two generic approaches to this. One is to simply createt he 'Audit" db with the same table structures as the 'active db'. When a record is created, updated or deleted, copy the record to the appropiate table in the audit db. In some instances, the audit tables have ddate/time and user name fields added and these are also 'filled in' when the audit record is written.

The other approach is to copy each field which is changed when ever a record is altered in any way (this includes record addition and deletion). This approach is shown as a (generically) workable function in faq181-291. PROPERLY implemented, either process should reasonably preserve any and all committed changes.

I have not posted a routine to 'rebuild' a table from the audit trail developed in the faq, however it should be a trivial exercise.

In either approach, you should take some care re the audit trail tables themselves, as they tend to grow somewhat more quickly than the actual recordset tables. I would also caution all who have sensitive data to check EXTENSIVELY with the 'net cops' to be sure the 'routine' backups are actually accomplishing a copy of the db. Win (and therefore most of the commercial back-up systems) will not copy (e.g. BACK UP) a file with any connections, so ONE individual working late or just leaving the app open duriing the back up period will generally cause the back up to skip the file. Further, the local 'net cops' are not generally inclined to review the back up log(s) or (even when they do) inform the 'owner' of the file that a back up was not successful. Add the issue of who is the 'registered' owner of the file (quite possibly -or probably- NOT the 'programmer' responsible for its care and feeding) and you can easily be 'blind sided' on hte critical date when something does go 'bump in the night'.

Other generic issues can, will and do affect your carrear path when dealing with the 'net cops', such as whn you use date/time data types in your records in the multiuser environment, you need to be assured either that the app is using the 'server time' or at least ALL the machines which access the app are FORCED to occassionally (at least DAILY) sync the clocks. I have seen at least on LARGE company where there was no thought (much less effort) to sync the clocks and found systems witth DAYS difference in the system time. Think of the trouble which could arise from a a billing soloution where one machine entered the start time and another the end time of a process. You could end up billing WAY to much -or the reverse. Neither ecpected to enhance YOUR carrear opportunities!



MichaelRed
m.red@att.net

Searching for employment in all the wrong places