Major Db corruption - causes??

[Malcy]

Hi
I have just been out to client with major db failure. We were getting a "network or disc error" message, and later one referring to "cyclic redundancy error" but since both back end and front end on one PC with no network assumed it to be disc error.
I narrowed it down to one table (inevitably the largest). It wouldnt let me copy or make a new table or append to a table.
The backups were duff but fortunately I had a three week old backup from a previous visit. I eventually managed to get it to append records that were not in my backup into a new table - i.e. it wouldn't copy all but it was happy enough copying the final 4,500 records in the table.
So with a lot of jiggery pokery I managed to re-create a valid and up to date backup and re-installed the FE and BE onto a loan PC I had brought in.
I brought the errant laptop home to work on it and on doing a full Chkdsk it has come up with "Windows replaced bad clusters in file 29150 of name \DATAFI~1\MICAPH~1\MGMMET~1.mdb which is exactly the file causing the problems.

So much for the background. The question I have is this likely to have been something my Access database could have done; is it likely to be operator negligence (they always say they shut down Access properly but you would say that wouldn't you) or is it more likely to be a hardware thing.
The database is running on Access 2003 on Windows XP Pro on a Dell Inspiron 1100

My main reason for needing to know it to try and identify best route forward. It has never happened on any of the other eight installations of the database so I don't think it is my programming (but prepared to accept I may be wrong). If it is hardware then should I condemn the laptop (its out of warranty) and suggest something more stable???
It isn't a confrontational issue - the client is so delighted I got it working again since he goes off on holiday tomorrow - but I do not want to have it happen again since it was two and a half hours of time I don't feel I can charge.
Any help or suggestion would be most valuable.
Thanks and best wishes

 

[boblarson]

How often is it being compacted? Also, are they trying to use it on a WAN or just on a LAN?

 

[Malcy]

Hi Bob
Thanks for the response
I set the front end to compact on close so at least daily. The back end gets compacted about once every six weeks or so - bit harder since so far as I can see I cannot compact it from the front end and I don't really want users opening up the back end to compact it.
There is no network involved in that both the FE and the BE are on the same hard disc of the laptop that they use. There is no network connection to anywhere. Only external connections are to printers and a dial up connection that isn't used much at all.
I did all sorts of scans this afternoon and no virus found (Symantec online scan) and no major spyware (a few minor bits from their occasional dial up internet use but nothing of consequence showing up in SpyBot and Ad-Aware).
It may be relevant that they hadn't done any Windows Updates so were on SP1a.
Have kindof felt the disc may be OK since after sorting initial bad clusters it passes muster on CHKDSK and also on Norton System Works optimisation, so brought it up to SP2 and did all the Windows Updates.
Does this shed any more light?

 

[boblarson]

I suspect that the backend needs to be compacted more frequently. There is code out there on the web that will let you compact a database from another one, so you might explore that option.

 

[Malcy]

Thanks Bob
I have done some searching and found that by combining VBA from all sorts of different sources I have been able to compact the back end from the front end (which I didn't think you could do) and then back it up and then zip it as well. All I now need to do is to find a way to copy it to a USB pen. Easy when the pen always comes up as the same drive but some users have all sorts of things so one day it might be the E:\ drive and the next it might be the G:\ drive.
Will continue to explore it further but thanks very much for your help.
Best wishes

 

[sfreeman@co.mer]

Malcy:

That really does sound like a hardware problem to me. Windows replaced bad clusters, so your physical drive was having issues, unfortunately your db file just happened to live there.

Since this is not on a LAN, a network type global backup probably will not work, but I would suggest that you inlcude some sort of regular backup routine of at least all tables at a convenient time each day.

I have several db's that I work on locally on my desktop before porting over to our LAN, and I have a Windows task scheduled to automatically run each day at noon [when I am at lunch] that will backup each of my dbs to an external USB type hard drive. Cost approx. $150 [US], but sure comes in handy when issues like his pop up.

Best regards...

 

[Malcy]

Thanks Sam
I admit that was my first thought but on running through all sorts of disc utilities it does seem to be working OK.
The backup procedure I outlined is now included in the new version of my software but this will not be finished for another few months. Meanwhile I have suggested very strongly that they do backups each day and that they verify those backups as well!
If it happens again then I will advise that hardware needs to be modified.
Thanks for your help
Best wishes

 

[ghudson]

I have done some searching and found that by combining VBA from all sorts of different sources I have been able to compact the back end from the front end (which I didn't think you could do) and then back it up and then zip it as well.

Would you mind post the code you are using to compact the back end from the front end. I would be interested to see how you are doing it with VBA. Thanks!

 

[Malcy]

OK
Please don't laugh too loud if any of it is too horribly clunky but it does seem to work. Thanks to all those who inspired is attributed but more importantly, appreciated.
Apologies for length but thought more help to put the whole sub in.

Private Sub cmdBackUp_Click()
On Error GoTo Err_cmdBackup_Click
    Dim strSource As String
    Dim strDest As String
    Dim strError As String
    Dim strMsgComplete As String
    Dim strTitleComplete As String
    Dim lngDbOp As Long
    Dim stDocName As String
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intDayNo As Integer
    Dim strBack As String
    Dim strBack1 As String
    Dim strWinZip As String
    Dim strZipFile As String
    Dim strFileToZip As String
        
''' Code originates from a variety of sources including ghudson, Roger Carlson and Oldsoftboss. Thanks
' Identify the user identity
    lngDbOp = [Forms]![frmLogonDbm]![txtOp]
        
' Define the message content
    strMsgComplete = "The compact, backup and zip of the MicaPharm file was successful."
    strTitleComplete = "MicaPharm Backup Complete"

' Identify the day of the week
    intDayNo = DatePart("w", Date)

' Close the logon form
    stDocName = "frmLogonDbm"
        DoCmd.Close acForm, stDocName, acSaveNo

' Verify that the backup directory exists and if not, create it



'' Compact the backend database
' Path where backend database is located
    strSource = DLookup("strBePath", "tblFilePaths")
' Path for temporary backup file
    strBack = DLookup("strBackupDb", "tblFilePaths")
' Path for backup file
    strBack1 = DLookup("strBackup1Db", "tblFilePaths")
    
    'Compact the Back-End database to a temp file.
    DBEngine.CompactDatabase strSource, strBack

    'Delete the previous backup file if it exists.
    If Dir(strBack1) <> "" Then
        Kill strBack1
    End If

    'Rename the current database as backup and rename the temp file to the original file name.
    Name strSource As strBack1
    Name strBack As strSource

'' Backup the database
BeginBackup:
  
  DoCmd.Hourglass True
        
' Path where backend database is located
    strSource = DLookup("strBePath", "tblFilePaths")

' Destination where data file is to be copied
    Select Case intDayNo
        Case 1
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSun.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSun.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSun.zip"
        Case 2
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackMon.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackMon.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackMon.zip"
        Case 3
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackTue.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackTue.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackTue.zip"
        Case 4
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackWed.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackWed.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackWed.zip"
        Case 5
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackThu.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackThu.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackThu.zip"
        Case 6
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackFri.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackFri.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackFri.zip"
        Case 7
            strDest = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSat.mdb"
            strFileToZip = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSat.mdb"
            strZipFile = DLookup("strBackPath", "tblFilePaths") & "\" & "MgmMcBeBackSat.zip"
    End Select
        
' Check to see if a backup file of the same name already exists, and if so, delete it
    If Dir(strDest) <> "" Then
        Kill strDest
    End If

' Copy the backend database to the backup destination
    FileCopy strSource, strDest
        DoCmd.Hourglass False
  
  
'' Create a zip file copy

' Define location of the WinZip programme
    strWinZip = "C:\Program Files\WinZip\WinZip32.exe"

' Check to see if a zip file of the same name already exists, and if so, delete it
    If Dir(strZipFile) <> "" Then
        Kill strZipFile
    End If

' Run the WinZip process
    Call Shell(strWinZip & " -a " & strZipFile & " " & strFileToZip, vbNormalFocus)
    Pause (5)
' Note, if a paid version of WinZip change vbNormalFocus to vbHide

' Full backup process has completed - Give Successful Completion Message
      MsgBox strMsgComplete, vbInformation + vbOKOnly, strTitleComplete
        
    
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        
' Record the details in the database management log
    rst.Open "tblDbmLog", cnn, adOpenDynamic, adLockOptimistic
        With rst
            .AddNew
                .Fields("dtmDbm") = Now()
                .Fields("lngDbmOp") = lngDbOp
                .Fields("lngDbmAct") = 5
            .Update
                        
            .AddNew
                .Fields("dtmDbm") = Now()
                .Fields("lngDbmOp") = lngDbOp
                .Fields("lngDbmAct") = 4
            .Update
            .Close
        End With
    
    stDocName = "frmControlPanelDbManagement"
        DoCmd.Close acForm, stDocName, acSaveNo
  
  
Exit_cmdBackup_Click:
  Set rst = Nothing
  Set cnn = Nothing
  stDocName = vbNullString
  strSource = vbNullString
  strDest = vbNullString
  strMsgComplete = vbNullString
  strTitleComplete = vbNullString
  strBack = vbNullString
  strBack1 = vbNullString
  strWinZip = vbNullString
  strFileToZip = vbNullString
  strZipFile = vbNullString
  Exit Sub

Err_cmdBackup_Click:
  Select Case Err.Number
    Case 61
      strError = "The Floppy Disk is full, Cannot Save to this Disk." _
        & vbCrLf & vbCrLf & "Insert a New Disk then Click ""OK"""
      If MsgBox(strError, vbCritical + vbOKCancel, " Disk Full") = vbOK Then
        Resume BeginBackup
      Else
        DoCmd.Hourglass False
        Resume Exit_cmdBackup_Click
      End If
    
    Case 70
      strError = "The File is currently open." & vbCrLf & _
        "The File can not be Backed Up at this time."
      MsgBox strError, vbCritical + vbOKCancel, " File Open"
        DoCmd.Hourglass False
        Resume Exit_cmdBackup_Click
    
    Case 71
      strError = "There Is No Disk in Drive" & vbCrLf & vbCrLf & _
        "Please Insert Disk then Click ""OK"""
      If MsgBox(strError, vbCritical + vbOKCancel, " No Disk") = vbOK Then
        Resume BeginBackup
      Else
        DoCmd.Hourglass False
        Resume Exit_cmdBackup_Click
      End If
    
    Case Else
      DoCmd.Hourglass False
      MsgBox Err.Number & vbCrLf & Err.Description
      Resume Exit_cmdBackup_Click
  End Select
    
End Sub

 

[ghudson]

You code look fine!

Thanks for posting your routine. I have seen that compacting method before. I am still searching for a compacting method that does all the work so that you do not have to worry about deleting the original and replacing it with the newly compacted "temp" file. I am uncomfortable with that delete and replace process.

Glad to see my old backup and zip routine being put to good use.

 

[Malcy]

Yep it does seem a bit drastic but I guess that is what Access does, albeit hidden.
Best wishes