Corrupt Table

[Pam]

I found the following while doing a search for Access corrupt
tables. I'm not sure where to apply the code to check my table. We had a
ripple run thru the db and errors showed in each field for this table for a
specific record. Thought it was corrected, but now when that table is
joined in a query, it can't be updated. No problems before the error. Will
someone please tell me how to apply the code to check this table? Your time
and help is greatly appreciated!!

Thanks, Pam

(Q) How can I recover records from a corrupt table?

(A) First, using Access while in the database window, copy the
table's structure only from the corrupt table, if possible. Then using the
code below, copy each individual row from the old table to the new one until
it encounters an error. The error routine displays the error, skips one row,
moves to the next and Resumes at Addit where it continues to move data from
the old table to the new table one row at a time.


' ********* Code Start ***********
' This code was originally written by Norm Chezem
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Norm Chezem
'
Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
On Error GoTo err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("tbl_Reservations")
Set NewRes = db.OpenRecordset("tbl_New_Res")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![ResID] = OldRes![ResID]
'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
NewRes.Update
RecCount = RecCount + 1
DoEvents
If RecCount Mod 10000 = 0 then
MsgBox RecCount 'Show progress every 10,000 rows
End If
Loop
MsgBox RecCount 'Show total successful record count
OldRes.Close
NewRes.Close
db.close
Proc_Exit:
Exit Function
Err_Proc:
MsgBox "<Error>" & Error$
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function
' ********* Code End ***********



© 1998-2006, Dev Ashish & Arvin Meyer, All rights reserved. Optimized
for Microsoft Internet Explorer

 

[Ken Snell \(MVP\)]

Before you resort to running a program, have you tried importing the corrupt
table into a new database (using File | Get External Data | Import from the
new database)? See if that will allow you to "copy" the table to a new
database. If it works ok, then you should be able to reimport the table from
the new database back into the original database.

The trick (no matter whether you use programming or manual efforts) is that
you'll need to reestablish all relationships in Relationships window to the
new table.

--

Ken Snell
<MS ACCESS MVP>

I found the following while doing a search for Access corrupt
tables. I'm not sure where to apply the code to check my table. We had a
ripple run thru the db and errors showed in each field for this table for
a specific record. Thought it was corrected, but now when that table is
joined in a query, it can't be updated. No problems before the error.
Will someone please tell me how to apply the code to check this table?
Your time and help is greatly appreciated!!

Thanks, Pam

(Q) How can I recover records from a corrupt table?

(A) First, using Access while in the database window, copy the
table's structure only from the corrupt table, if possible. Then using the
code below, copy each individual row from the old table to the new one
until it encounters an error. The error routine displays the error, skips
one row, moves to the next and Resumes at Addit where it continues to move
data from the old table to the new table one row at a time.


' ********* Code Start ***********
' This code was originally written by Norm Chezem
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Norm Chezem
'
Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
On Error GoTo err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("tbl_Reservations")
Set NewRes = db.OpenRecordset("tbl_New_Res")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![ResID] = OldRes![ResID]
'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
NewRes.Update
RecCount = RecCount + 1
DoEvents
If RecCount Mod 10000 = 0 then
MsgBox RecCount 'Show progress every 10,000 rows
End If
Loop
MsgBox RecCount 'Show total successful record count
OldRes.Close
NewRes.Close
db.close
Proc_Exit:
Exit Function
Err_Proc:
MsgBox "<Error>" & Error$
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function
' ********* Code End ***********



© 1998-2006, Dev Ashish & Arvin Meyer, All rights reserved. Optimized
for Microsoft Internet Explorer