Results 1 to 10 of 10
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Backup / Corruption (Access 2003)

    I have written a system that is used by some auctioneers. The system is run on a laptop that can be taken to the site of the auction. It is a split db, with both the FE and BE on the same laptop.

    A spare laptop is taken in case there is some catastrophic crash., and I tell them to backup regularly, so they if they have to swap computers they won't lose much data. They backup to a memory stick, using a command button on the menu.

    To avoid them having to deal with the memory stick filling up, and ending up with lots of different versions of the backend file, each backup overwrites the previous, using the same file name as the original on the hard disk.

    Yesterday, just after they finished cataloguing an auction, they did a backup, but immediately after that both the original BE file, and the copy on the memory stick, were corrupt. The system would not run, and trying to open either data file directly, resulted in the message "Unrecognised database format."

    Here is the code that does the backup, using the apiCopyFile. I have used it myself many times without a problem.

    I wondered if the problem was caused by removing the memory stick while the copy was still in progress. They assure me that they did not remove it quickly, but I don't really know. I tested it with my memory stick and I could not cause a problem that way. No matter how quickly I pulled it out the whole data file was copied.

    Any suggestions about why this might have happened?

    Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
    (ByVal lpExistingFileName As String, _
    ByVal lpNewFileName As String, _
    ByVal bFailIfExists As Long) As Long
    Sub BackupData()
    '---------------------------------------------------------------
    ' PURPOSE: Copy a backend data file to another. location
    ' ' RETURNS: Nothing
    '---------------------------------------------------------------
    Dim strSourceFile As String
    Dim strSaveFile As String
    Dim Result As Long
    Dim dlgFileDialog As Object
    Set dlgFileDialog = Application.FileDialog(msoFileDialogSaveAs)
    strSourceFile = fnGetBackendDataFile
    strSaveFile = strSourceFile
    If Dir(strSourceFile) = "" Then
    MsgBox Chr(34) & strSourceFile & Chr(34) & " is not valid file name."
    Exit Sub
    End If
    With dlgFileDialog
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = strSaveFile
    .AllowMultiSelect = False
    .Title = "Backup Location"
    End With

    If dlgFileDialog.Show = -1 Then
    ' then the user clicked the action button
    strSaveFile = dlgFileDialog.SelectedItems(1)
    If Len(strSaveFile) > 0 Then
    DoEvents
    DBEngine.Idle
    DoEvents
    Result = apiCopyFile(strSourceFile, strSaveFile, False)
    End If
    Else
    MsgBox ("You chose to Cancel, so the data was not backed up.")
    End If
    Set dlgFileDialog = Nothing

    End Sub
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Backup / Corruption (Access 2003)

    It's probably an unfortunate glitch, and I doubt very much that you'll be able to find the cause.

    If there is enough room on the memory stick, you could store a series of backups, start with Backup1.mdb, then Backup2.mdb, up to - say - Backup5.mdb, then start over with Backup1.mdb.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Backup / Corruption (Access 2003)

    I suspected as much, but the auctioneers are not very receptive to the "unfortunate glitch" line.

    I tell them to backup for security, and the result is the exact opposite.

    I will try your suggestion.

    I was also thinking of creating a backups folder on the hard disk, and first backing up to there, then copying that to the memory stick. I thought that maybe if the problem is the stick, this would isolate the original file from the problem.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Backup / Corruption (Access 2003)

    Excuse my ignorance, but is the memory stick attached to the USB port?

    If so, they may have to change the writing to the stick from cacheing to immediate.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Backup / Corruption (Access 2003)

    Yes Pat, it is attached to a USB port.

    I read the startpost about Safe Removal of USB drives, and wondered if that could be related to this issue.

    I have not seen the offending computer since the incident happened (the auctioneers were in Sydney for a few days), but I have used the memory stick in the past and don't remember any delay in accessing data on it.


    If the problem was caching of the data, that would not explain the corruption of the original data file ( i would not htink).
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Backup / Corruption (Access 2003)

    Fair point, and I was alluding to that starpost.

    Any chance of getting that rogue laptop and testing it yourself.

    Maybe you would be better to make different names of the copied databases, say one for each day of the week.

    So your backup strategy could be to copy databasename (name of your database) to databasenameDDD where DDD is the short day of the week. This would be fine as long as ou have enough room on the stick. Hans has already suggested this method.

    What Charlotte suggested in another thread was to compact the BE database to a different database name. This may by a way to go. But as you say both databases were corrupt, suggesting that the live database was corrupt prior to copying it to the stick.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Backup / Corruption (Access 2003)

    Thanks Patt

    I will try to get to use the machine next week.

    When they are in the middle of an auction I want to encourage them to backup very often - say every fifteen minutes, for a few hours (sometimes a few days), so that if they have a crash and have to swap computers they don't lose much. While an auction is under way, they are entering prices and bidders, creating invoices and receiving payments. Usually with a queue of people waiting to pay!. Any lost data will cause great confusion.

    Also if they have to swap computers, I don't want there to be any doubt about what is the most recent backup, as they will have to perform the swap on their own, very quickly. So that is why I went for each backup overwriting the previous one , all with the same filename.

    After this event, I suspect they won't backup at all!
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Backup / Corruption (Access 2003)

    It's almost like you need a restartable database. You would create records on the fly in a 2nd database as well as the live database. So if you had to go back to a backup you would just replay all the transactions from the 2nd database (or txt file) against the live database.

    I used to support the Tab's betting system years ago and had to replay the transactions from the start of day state of the database if the raceday crashed.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Backup / Corruption (Access 2003)

    This has just happened again, and I think I now what know causes it (in this 2nd case at least.)

    When the dialog box appears for the user to select the backup location, they don't select the memory stick. Instead they use the same folder as the database is located in. The apiCopyFile function tries to copy the data file back on itself. This results in all data being lost.

    So I am now rewriting my code to do three things:
    1 Check that the Save file is different from the Source file. Display a Message and Exit if they are the same.
    2 Make a Backup folder (if it does not exist) and use that as the default location.
    3 Copy the file to a temporary file on the hard disk before trying to copy to a memory stick, so that if the user pulls out the stick in the middle of the backup the original file is protected.
    Regards
    John



  10. #10
    New Lounger
    Join Date
    Jun 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backup / Corruption (Access 2003)

    I had some cases with a USB stick where copying a file to a USB stick would create a corrupt file of the copied file. I found out that this could happen if:

    1. The USB stick pins does not make full contact to the port. This can happen due to extra weight which pulls the stick downwards especially if the stick is attached to the home, car keys. Also to sudden movement of the stick which can make the stick partially lose the contact with some of the USB port pins. It could also be due to bad pins in the USB port.
    2. The computer is infected with spyware software (especially the "Alexa" spyware).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •