Accident prevention (2000)

Topic

New Reply

I had an incident today that has me looking for ways to prevent it happening in the future. Here’s the basics:
1) Small club database to record membership info and revenues
2) Multiple volunteers using a win98 machine
3) Access is accessible to anyone who turns on the machine
4) All our data entry of the last 3 months was lost when someone accidentally restored the July 31 backup from Winzip into the folder where the backend resides.

Sadly, and stupidly on my part, there was no later backup. This came about because the woman who was my co-volunteer and took care of backups every time new data was entered, passed away suddenly and unexpectedly in August. I have been scrambling to keep things going and neglected the backups (trust me, if nothing else gets done, that won’t happen again).

Given that there is no real security, etc is there anyway to have prevented this given the scenario above? We have a newer donated machine which I believe is either XP or 2000, so I am seriously thinking about logons so Access is only available to those who are trained to use it.

The data loss can all be recreated but we are looking at 10 hours or so of labour to do it. For the time being, any easily implemented suggestions that will work with Win98 would be appreciated. In the meantime, steps have been taken to make people a little more careful. Perhaps an automatic copy made via VBA everytime the database is closed and named with the date? What would this sort of code look like.

Cheers

Reply | Quote

Replies

The problem is not just with Access. Anyone can delete a database, spreadsheet or document. I’d look for a more general backup solution.

If you prefer an Access-only backup, do a Google search for microsoft access automatic backup. You’ll find

Reply | Quote

You need better backup procedures. First of all, you don’t want a procedure that always backs-up over the same file. So, for example, if you backed-up once a week, you should not write-over that same backup each week, rather you should back a new file (keeping at least a month’s worth). Secondly, the backed-up files should not reside on your machine. Write them to a CD, or copy them to a memory stick which you can off-load to another machine off-site, or use an internet backup service.

Reply | Quote

Thanks to both of you. The old procedure actually worked quite well as my colleague emailed a copy of the zip to herself and myself. There is nothing particularly sensitive in the data, the need is to guard against exactly the sort of mistake that happened. My thinking was exactly what you are suggesting Mark, in that the backups would not overwrite each other but saved with the current date as part of the file name. Thanks for the suggestions.

Reply | Quote

Peter,

Here is a snippet of code to backup using a control. You could of course add it to the on close event, but that might not be a good idea.
Private Sub Cmd_BackupBackEnd2_Click()

Dim strFileName As String
Dim strSourcePath
Dim strDestPath
Dim SourceFile As String
DestinationFile As String
Dim TodayDate As String
Dim strPath As String

TodayDate = Format(Date, “MM_DD_YY”)
strSourcePath = “C:MyDir”
strDestPath = “F:MyServer”
strFileName = “dbMyDatabase_be”

‘ Back End
SourceFile = strSourcePath & strFileName & “.mdb” ‘ Define source file name.
DestinationFile = strDestPath & strFileName & TodayDate & “.mdb” ‘ Define target file name.
FileCopy SourceFile, DestinationFile ‘ Copy source to target.

End Sub

You can add code to test for existing backup with same name and prompt user if you want. However, don’t ignore Hans’ and Mark’s recommendations.

I hope this helps.

Ken

Reply | Quote

Thanks for this. I probably will add it in to the close event simply for expediency’s sake and safety. Regardless, I will still do some sort of offsite backup as well. The nice thing is that this database, except for adding in new members for beginners’ classes 3 times a year is really only used once per month to record the revenues and print reports for head office. The local backup will go into a buried folder somewhere that only the most maliciously curious will find.

Reply | Quote

Hi Ken:

I tried your code but it fails with error 70. I looked at FileCopy in help and it states that you cannot copy an open file. So how do I copy the backend from within the database if it is actually open? I did find some other code to do this fairly simply on the web, but your was so nice and straightforward. I’m testing this at home under WinXP Pro. Perhaps this isn’t an issue on Win 98 which is what the club computer has?

Reply | Quote

You’d have to close all forms bound to tables in the backend (directly or indirectly through a query) and all recordsets based on tables in the backend (directly or indirectly through a query) before running the code.

Reply | Quote

So if I am using a switchboard and closing the whole database from the switchboard, where would you suggest I put the code? Could I alter my “exit” button on the switchboard to merely close the switchboard form and then call some code from a standard module to backup, then close the database?

Reply | Quote

That ought to work – it’s certainly worth a try.

Reply | Quote

Well I have it narrowed down. As long as no forms are open, I can run the code manually from a module. What I can’t quite get to work is the timing of getting it to run itself automatically. I tried attaching the calling to the code to a separate unbound form whose only purpose in life is to call this code on close and it still won’t work. It seems until the OnClose event of a form is completed, then the form is officially still open (which makes some logical sense ).

However, how do I call this code from something that isn’t a form?

It seems to be a bit of a catch 22. I’m sure there must be a reasonably simple solution but I’m not seeing it.

Reply | Quote

I’d use something different. The search I suggested in my first reply returns this (amongst many others): Compact a List for MS Access from Peter’s Software (free).

Reply | Quote

Others have discussed the need for a better backup scenario. You might want to look at FileHamster
http://www.mogware.com/FileHamster/%5B/url%5D that automatically keeps multiple, dated backups. Free, and worth a look.

Reply | Quote