Corruption nightmare

[Remster]

Hi Folks

My database has only three users: me, my boss, and a colleague who works from home. Since starting to use the database, we've discovered that my colleague's network connection is extremely unreliable, and yesterday she lost connection while working on a record and the database became corrupted. Fortunately I'd made a backup copy the previous evening and she'd done little work before the problem arose, but we can't expect that always to be the case.


So I have two questions:

1. What's the best way to protect against corruption by this means? (I've read about 'splitting the database', but what I've read warns against doing this without a level of technical competence that I lack.)
2. Is there a simple way to get backup copies made automatically? (I've read about creating 'batch files', but I can't get anything I've read about to work.)

For information, our IT department offers no technical support for Access, so I'm on my own here.



Thanks

Remster

[ajetrumpet]

i'm not sure if this will help, but you can give it a try:

Function BackupSource()

'******************************************************************************
'                                                                             *
'Author: Unknown                                                              *
'Modified By: Adam Evanovich                                                  *
'Date: 9/25/2010                                                              *
'Purpose: To backup the source BackEnd file of a database                     *
'                                                                             *
'Arguments: None                                                              *
'                                                                             *
'******************************************************************************

On Error GoTo Err_BackupSource

Dim strBu As String
   Dim buf As String
      Dim MD_Date As Variant
         Dim fs As Object
            Dim strSourceName As String
               Dim strSourceFile As String

                  Const conPATH_FILE_ACCESS_ERROR = 75

strSourceName = CurrentProject.Name
strSourceFile = CurrentProject.Path
buf = CurrentProject.Path & "\Backups\"

   If GetAttr(buf) <> vbDirectory Then
      MkDir buf
   End If

Continue:

      MD_Date = Format(Date, "yyyy-mm-dd ") & Format(time, "hh-mm-ss")
      strSourceFile = CurrentProject.Path
      strBu = CurrentProject.Path & "\Backups\" & MD_Date & "\"
   
         MkDir (strBu)
         
            Set fs = CreateObject("Scripting.FileSystemObject")
               fs.CopyFile strSourceFile & "\" & strSourceName, strBu
            Set fs = Nothing
 
'Successful
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successful!", _
        vbInformation, "Backup Successful"
 
Exit_BackupSource:
  Exit Function
 
Err_BackupSource:
  If err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & strBu & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
      If err.Number = 53 Then
         MkDir buf
            GoTo Continue
      Else
         MsgBox err.Description, vbExclamation, "Error Creating " & strBu
      End If
  End If

End Function '//LL

[Remster]

It works! I'm currently having backups made whenever the switchboard (and so the database) is closed. In time I want to have the database compacted and repaired, a backup made, and old backups deleted, when the database is opened for the first time in a given day, but at the moment I can't work out how to do this.

Since all my housekeeping is manual at the moment, could your code be adapted so that the backups are added to zip files instead?

[KathyL]

Hi Folks

My database has only three users: me, my boss, and a colleague who works from home. Since starting to use the database, we've discovered that my colleague's network connection is extremely unreliable, and yesterday she lost connection while working on a record and the database became corrupted. Fortunately I'd made a backup copy the previous evening and she'd done little work before the problem arose, but we can't expect that always to be the case.


So I have two questions:

1. What's the best way to protect against corruption by this means? (I've read about 'splitting the database', but what I've read warns against doing this without a level of technical competence that I lack.)

Remster

It says you're running Access 2003. What level is the actual DB in, Access 2000, 2002, or 2003 format?

Check to be sure all clients have current Service Packs installed (SP3) . I've seen when lack of current service packs can lead to corruptions.

[Remster]

Thanks, all.

What I have now is good enough. My DB is being compacted once every day wherever changes have been made to the data since the DB was last compacted, and back-ups are being made wherever a user closes the DB having made modifications. If we go for a few months without any problems, I'll merge the backing up with the compacting, so I'll have fewer back-ups to think about deleting.

For anyone who's interested, here's how I'm doing it. I have a table called 'ControlTable' with only one record and three fields: 'LastModified', 'LastBackedUp' and 'LastCompacted'. In the AfterUpdate event of each of my forms and subforms I have the following code:

 
strSql = "UPDATE ControlTable SET ControlTable.LastModified = Now();"
DoCmd.SetWarnings False
CurrentDb.Execute strSql, dbFailOnError
DoCmd.SetWarnings True

The corresponding code for LastBackedUp is in the Unload event of my switchboard, while the code for LastCompacted is in its Load event.

ajetrumpet's procedure is called if a user closes the switchboard (thereby closing the DB) when LastModified is greater than LastBackedUp. And if a user opens the DB when LastModified is greater than LastCompacted and LastCompacted is less than today's date, the following procedure is called:

 
Function CompactDatabase()
   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
   accDoDefaultAction
End Function

This is also called (followed by the back-up procedure) if LastModified is greater than LastBackedUp owing to an incorrect closing of the DB previously.