Wally wrote:
Hi there,
One table is corrupted. I found compacting is the cause or triggers
the process. I have the solution to repair the wrong records but after
each compact the same table is corrupted again at the same place.
After repairing the table by cutting rows and appending data from
outside (sql from coldfusion or from Excel), user may appends row(s)
in this table. Until compacting everything is fine with those new
records. After compacting, the table opens great but any process using
this table makes db crashing (Access Not Responding). Does anyone can
help on the cause? Thanks.
Is there some field that has invalid data in your appends? You really
need to find what records are causing your problem
Are you sure you are finding all of the wrong records from above?
Here's a routine that will scan all tables and all fields in your
database. You need to create a table, Table1, with the fields TableName
and RecordCount. If it aborts/bombs out you have the last table
accessed and the last record number of the table it could access
correctly. So if you saw the last table in Table1 is TableABC and the
rec count of 40 and there was 500 records in TableABC, you know record
41 is corrupted/bad in that table.
Sub ScanAllTableFields()
Dim tdf As TableDef
Dim r As Recordset
Dim r1 As Recordset
Dim l As Long
Dim i As Integer
Dim v As Variant
Set r1 = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
For Each tdf In CurrentDb.TableDefs
If Left(tdf.name, 4) <"MSys" Then
Set r = CurrentDb.OpenRecordset(tdf.name, dbOpenDynaset)
If r.RecordCount 0 Then
l = 0
r.MoveFirst
Do While Not r.EOF
For i = 0 To r.Fields.Count - 1
v = r(i).Value
Next
l = l + 1
If l <1 Then
r1.Edit
Else
r1.AddNew
End If
r1!TableName = tdf.name
r1!RecordCount = l
r1.Update
r1.Bookmark = r1.LastModified
r.MoveNext
Loop
Else
r1.AddNew
r1!TableName = tdf.name
r1!RecordCount = 0
r1.Update
End If
r.Close
Set r = Nothing
End If
Next tdf
r1.Close
Set r1 = Nothing
MsgBox "Done"
End Sub
