Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: detecting database corruption

    I'm using Access 2003.

    Recently I've had a database file become corrupted and was unable to open or repair it. I replaced it with a backup and later found that a number of records in the backup are corrupted.

    The records still exist but come up in table datasheet view as #Error and aren't retrieved by queries. While in table datasheet view, I get the error message "Not a valid bookmark.". Also note that not all records in the table are corrupt, in fact it is about 10,000 out of 50,000 records.

    I have since switched to an older backup but it concerns me that perhaps this backup also has corruption in another table and I've just not found it.

    What I am wondering is if there is a way to quickly or programmatically check for corruption, instead of going through 50 odd tables full of up to 400,000 records every now and then?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Very good question. I don't have a good answer for that one.

    I guess the only advice I can give is that if it gets to the point where you have to check so much data over so many tables is that it's time to migrate the data to SQL Server.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you have an autonumber field in the table?

    I've seen this kind of corruption without an autonumber field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens when you and open a recordset and cycle on to one of these corrupt records?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2006
    Posts
    72
    Very good question. I don't have a good answer for that one.

    I guess the only advice I can give is that if it gets to the point where you have to check so much data over so many tables is that it's time to migrate the data to SQL Server.
    We've considered migrating to SQL Server, PostGRESQL or Oracle but at this point Access is working well for us despite it's drawbacks.

    Do you have an autonumber field in the table?

    I've seen this kind of corruption without an autonumber field.
    There is no autonumber. The primary key is as an example "2006-1-1-1" which represents year-series-trial-treatment.

    What happens when you and open a recordset and cycle on to one of these corrupt records?
    I wondered the same thing and set up a program to do that. While I was waiting for it to run through the recordset and get to the corrupted records though I noticed that the recordset count included the corrupted records.

    Comparing the record count of a query to the recordset for the table gave me a way to detect the corruption because the query would not count the corrupt records. I wrote this program to do this on all tables...

    Code:
        Dim Rst, Qry As DAO.Recordset
        Dim i, RCount, QCount As Long
        txtCompare = ""
        For i = 0 To CurrentDb.TableDefs.Count - 1
            DoEvents
            SQL = "SELECT * " _
                & "FROM [" & CurrentDb.TableDefs(i).Name & "]"
            Set Qry = CurrentDb.OpenRecordset(SQL)
            Set Rst = CurrentDb.OpenRecordset(CurrentDb.TableDefs(i).Name)
            If Not Rst.EOF Then
                Rst.MoveLast
                RCount = Rst.RecordCount
                Qry.MoveLast
                QCount = Qry.RecordCount
                txtCompare = txtCompare & CurrentDb.TableDefs(i).Name & " Rst = " & RCount & " Qry = " & QCount & IIf(RCount <> QCount, "*********** CORRUPTION *************", "") & vbNewLine
            Else
                txtCompare = txtCompare & CurrentDb.TableDefs(i).Name & " Rst = 0 Qry = 0" & vbNewLine
            End If
        Next
        MsgBox "Done."
    Unfortunately while I did find a number of legitimately corrupt tables the program also returned a number of tables that actually had more records in the query than in the recordset. I am going to look into this myself but I am wondering if anyone has any idea why there might be more records in a query than a recordset for a table.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Never have I WANTED to have an MDB with corruption in it to play with... but I do now! ^^

    There is no way a query could return MORE records than the table... well, not without relating it to something else. I suspect further corruption tbh.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    knock on wood I have only had 1 database I was corrupted and that was given to mw

    it was a memo feild 1 record (over 200meg insize) once I delete it went do to 25meg

    but one thing i do in the back end I create a Autoexec to close the database
    then set the msaccess to compact/repair on close on my schedule PC write a Scheduled task to run every day about 2.00am to just open it autoexec kick in close it and the compact/repair tack over.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hmmm... when I run your code, I get the same anomaly on one table -- tables suppsedly having less records than the query.

    But I'm afraid that there's a flaw. The code returns a number representing something, but it's not the total recordcount for a table.

    With my anomalous table, I opened the supposed corrupt table and found that it has exactly the right number of records.... the same number as reported by the query count.

    I'm at a loss as to why. The code checks out ok from what I can tell. Speaking of which, here's a revamp of the code:

    Code:
    Private Sub CheckForCorruption_Click()
        On Error Resume Next
        Dim rsT As Recordset
        Dim rsQ As Recordset
        Dim i As Long, TCount As Long, QCount As Long
        Dim vOutput As String, vSQL As String
        
        For i = 0 To CurrentDb.TableDefs.Count - 1
            vSQL = "SELECT * FROM [" & CurrentDb.TableDefs(i).Name & "]"
            Set rsQ = CurrentDb.OpenRecordset(vSQL)
            Set rsT = CurrentDb.OpenRecordset(CurrentDb.TableDefs(i).Name)
            rsT.MoveLast
            TCount = rsT.RecordCount
            rsQ.MoveLast
            QCount = rsQ.RecordCount
            vOutput = vOutput & CurrentDb.TableDefs(i).Name & " : TABLE = " & TCount & ".  QUERY = " & QCount & IIf(TCount <> QCount, "   - Difference: " & TCount - QCount & ".   *********** CORRUPTION DETECTED *************", "") & vbNewLine
        Next
    
        Me.txtOutput = vOutput
        
        Set rsT = Nothing
        Set rsQ = Nothing
    End Sub
    Edit: I just found another table that does exactly the same. Clayton's Corruption?
    Last edited by StarTrekker; 04-09-08 at 00:49.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jun 2006
    Posts
    72
    I wanted to find what records were not being counted by the rst.recordcount since it might give a clue to why they are not being counted so I tried to be sneaky and do a movelast, confirm that it's got a count smaller than it should have and then loop backward through the recordset and output all records to Excel. Unfortunately I got the full set of records. I also tried the same thing from the start of the recordset going forward and got the full set.

    I can't think of any other way to look into this problem sneaky or otherwise. I suppose at least I have a semi functional method for finding database corruption but there's always a small chance that a table will have less records counted for some odd reason and that number matches the number of corrupt records in the table, making it appear to be ok. I suppose that's pretty unlikely to happen though.

    I will think some more about it and maybe try a couple more things but I think I've come as far as I can with this.

    Also, what is "Clayton's Corruption"?

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you have a database that definitely contains corrupted records, could you possibly upload it? I think the only way to truly detect corruption would be to attempt to read each record. This query vs table comparison method seems to be less than reliable, but it's hard to say when I don't have a table that's corrupted.

    Oh, and "Clayton's Corruption" is a reference to Clayton's advertising in the 80's... I was kinda saying that it's "the corruption you have when you're not having corruption"
    http://en.wikipedia.org/wiki/Claytons
    Guess ya had to be there ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jun 2006
    Posts
    72
    I've looked into it further and I've realized that since looping through and counting the table recordset always gives the correct number, I can compare that to the query's recordcount to get corrupted records and compare it to the tables recordcount to get records that are not counting for some reason.

    I have found that a compact and repair will fix both problems but if it finds corrupted records will delete them.

    What I am going to do is write a program to detect records that are corrupted and records that are not being counted and in the case of corruption, go to a reliable backup and in the case of records not counting right, do a compact and repair.

    Since this program must loop through the record set for every table it will take a while to run so I'll probably set it up to run as a scheduled task overnight and generate a text log file.

    One possible problem is that it might crash when it comes to corrupted records in the record set and if that is the case, I will try to find a way around it.

    The corrupted database I currently have takes up over 100mb so I will delete all but one of the tables with corrupted records and upload it for you to have a look at if you'd like.

    Finally once I've written the program to detect corruption or records that don't count right, I will also post the code here as an update.

    Thanks for all the help guys.
    Last edited by Access Junkie; 04-09-08 at 04:30.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by Access Junkie
    One possible problem is that it might crash when it comes to corrupted records in the record set and if that is the case, I will try to find a way around it.
    Just make sure you have error trapping in your code and you should be fine

    Quote Originally Posted by Access Junkie
    The corrupted database I currently have takes up over 100mb so I will delete all but one of the tables with corrupted records and upload it for you to have a look at if you'd like.
    Not sure if you can do that tbh... the file size will remain at 100MB until you compact and repair it, which will fix the corruption. Catch 22. I think you might have better success if you create a new database and import a table that has corruption in it. Feels weird trying to preserve corruption ^^

    Quote Originally Posted by Access Junkie
    Finally once I've written the program to detect corruption or records that don't count right, I will also post the code here as an update.
    Thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm pretty sure that if you create an autonumber field, your corruption will go away (it did for me). Although I can't recall if it fixed existing corrupted records but I can say that I didn't get any further corrupted records. I might have copied all the records to a new table with an autonumber field (but can't remember). Either way, I can say that not having the autonumber field is most likely the culprit.

    You should always have an autonumber field in ALL your data tables (although I believe it should be in all tables). An autonumber is not the same as a primary key (although often the autonumber is used as a primary key but there is a difference between the two.) Leave your primary key the same but add an autonumber field to the table, then compact/repair (and possibly re-index) - I can pretty much guarantee your corruption will go away.

    Many times I've come across table corruption problems (and forms not saving data problems), there is no autonumber (or indexing) for the table and I don't think MSAccess stresses enough the use of having an autonumber field.
    Last edited by pkstormy; 04-09-08 at 12:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jun 2006
    Posts
    72
    I tried to get a corrupt table on it's own but could not do it. If I try importing or copying the table, it deletes all the records whether they are corrupt or not. I cannot copy paste the corrupt records either.

    I wrote the program below to detect corruption and output it into a text log file. I used it last night and it worked well.

    Code:
    Public Function DetectCorruption()
        On Error Resume Next
        Dim RstN As DAO.Recordset
        Dim Qry As DAO.Recordset
        Dim i, LCount, TCount, QCount As Long
        Dim txtCompare, SQL As String
        Dim fs, f As Object
        
        'loop through all tables.
        For i = 0 To CurrentDb.TableDefs.Count - 1
            DoEvents
            'set sql
            SQL = "SELECT * " _
                & "FROM [" & CurrentDb.TableDefs(i).Name & "]"
            'set table and query objects
            Set Qry = CurrentDb.OpenRecordset(SQL)
            Set RstN = CurrentDb.OpenRecordset(CurrentDb.TableDefs(i).Name)
            'Count table records.
            RstN.MoveLast
            TCount = RstN.RecordCount
            'Count query records.
            Qry.MoveLast
            QCount = Qry.RecordCount
            'Loop through the table recordset and count records.
            LCount = 0
            RstN.MoveFirst
            Do While Not RstN.EOF
                DoEvents
                LCount = LCount + 1
                RstN.MoveNext
            Loop
            'Output counts
            txtCompare = txtCompare & CurrentDb.TableDefs(i).Name & " Table = " & TCount & " Query = " & QCount & " Loop = " & LCount
            'If a query has less records than the loop count, corruption is indicated
            If QCount < LCount Then
                txtCompare = txtCompare & " ***CORRUPTION***"
            End If
            'If the table recordset count doesn't equal the loop count, it is not counting records correctly
            If TCount <> LCount Then
                txtCompare = txtCompare & " ***Faulty Count***"
            End If
            'I have not encountered an instance where the query has more than the loop count
            'I don't believe this can happen but if it does, I'd like to know.
            If QCount > LCount Then
                txtCompare = txtCompare & " ***Anomaly***"
            End If
            txtCompare = txtCompare & vbNewLine
        Next
        Set RstN = Nothing
        Set Qry = Nothing
        
        'log results
        Set fs = CreateObject("Scripting.FileSystemObject")
        
        Set f = fs.OpenTextFile("C:\GPL Server\wheat\Annual Database\CorruptionLog.txt", 2, 0)
        f.Write Format(Now, "DD/MM/YY") & " " & Format(Now, "HH:MM:SS") & vbNewLine & vbNewLine & txtCompare
        f.Close
    End Sub
    Also if autonumbers avoid and repair corruption, I'll add them to some tables.
    Last edited by Access Junkie; 04-09-08 at 21:05.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah I thought it might be a tricky one.

    Thanks for the code update though, I'm gonna give it a try and see if I can find any undetected corruption
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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