Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Tables: Recover records from a corrupt table

Author(s)
Norm Chezem

(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-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer