By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,391 Members | 2,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,391 IT Pros & Developers. It's quick & easy.

Compact/Repair corrupts table

P: n/a
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.

Sep 13 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Sep 13 '07 #2

P: n/a
On Sep 13, 10:28 am, Wally <ro2st...@gmail.comwrote:
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.
You may have a corrupted database. Try creating a new blank database
and then importing all of the objects from the old one. That may
solve the problem. Don't forget to select options from the Import
Objects dialogue, and select Relationships and Menus.

Sep 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.