Code to fix corrupt table

[dixie]

I have come across some code to walk through the records of a corrupt table
and copy it record by record to a new table. It comes from the MVPS.Org
site and is as follows.
-----------------------
(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.OpenRecordse t("tbl_Reservat ions")

Set NewRes = db.OpenRecordse t("tbl_New_Res" )

RecCount = 0

OldRes.MoveFirs t

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 ***********

I have worked out that the NewRes![ResID]=OldRes![ResID] probably has to be
customised to reflect my unique record name.

What happens when I run this is I get an error for each row that occurs
whenever I have a [Required] field and it tells me that it can't be null as
it is a requred field.

I have the procedure in a module and I am simply typing CopyRes in the
Immediate window. I have changed the table names in it to suit my
situation. Can anyone see what I am doing wrong?

dixie

[pietlinden]

huh? This code is missing a serious chunk! Okay, assuming that the
code is copying field by field into the new table, you need to do
something like

'--add a new blank record to the destination table
rsDest.AddNew

'---populate all the fields in the new record from values from the
current record in the source table
for intCounter = 0 to rsSrc.Fields.Co unt-1
rsDest.Fields(i ntCounter) = rsSrc.Fields(in tCounter)
next intCounter
'---save the values in the new record
rsDest.Update

Okay, so then you have to call that for each record in the source
table, so you wrap it in a Do Until rsSrc.EOF thing and away you go.

[dixie]

The code is supposed to be complete and is at
http://www.mvps.org/access/tables/tbl0018.htm - I just altered the names of
the tables involved and then the line:
NewRes![ResID] = OldRes![ResID] which I took to be the uniqe ID number for
the table.dixie<pi* *******@hotmail .com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

huh? This code is missing a serious chunk! Okay, assuming that the
code is copying field by field into the new table, you need to do
something like

'--add a new blank record to the destination table
rsDest.AddNew

'---populate all the fields in the new record from values from the
current record in the source table
for intCounter = 0 to rsSrc.Fields.Co unt-1
rsDest.Fields(i ntCounter) = rsSrc.Fields(in tCounter)
next intCounter
'---save the values in the new record
rsDest.Update

Okay, so then you have to call that for each record in the source
table, so you wrap it in a Do Until rsSrc.EOF thing and away you go.

[dixie]

Sorry about the formatting of that last message. It just occurred to me
Pieter after I reread your message that the single line:
NewRes![ResID]=OldRes![ResID] is just an example of doing one of the fields
of the table. So, I do have to include all the fields as lines at that
stage of the code. There are a lot of fields in this table. I like your
code that seems just to automatically add each field. I presume I need to
dim intcounter as something before I can use that code - dim intCounter as
integer?

I'll have a play with it with a smaller table.

Thanks

dixie

<pi********@hot mail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

huh? This code is missing a serious chunk! Okay, assuming that the
code is copying field by field into the new table, you need to do
something like

'--add a new blank record to the destination table
rsDest.AddNew

'---populate all the fields in the new record from values from the
current record in the source table
for intCounter = 0 to rsSrc.Fields.Co unt-1
rsDest.Fields(i ntCounter) = rsSrc.Fields(in tCounter)
next intCounter
'---save the values in the new record
rsDest.Update

Okay, so then you have to call that for each record in the source
table, so you wrap it in a Do Until rsSrc.EOF thing and away you go.

[dixie]

Well, I must admit I've given up on making this work easily. I had a
corrupted table last week with over 15,000 records which I had to search
through for 2 corrupted records, then copy block by block the good messages
as I couldn't delete the corrupted ones. This is just so slow. If I could
have had this working, I am sure I could have pressed a button and made a
nice cup of tea.

dixie

<pi********@hot mail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

huh? This code is missing a serious chunk! Okay, assuming that the
code is copying field by field into the new table, you need to do
something like

'--add a new blank record to the destination table
rsDest.AddNew

'---populate all the fields in the new record from values from the
current record in the source table
for intCounter = 0 to rsSrc.Fields.Co unt-1
rsDest.Fields(i ntCounter) = rsSrc.Fields(in tCounter)
next intCounter
'---save the values in the new record
rsDest.Update

Okay, so then you have to call that for each record in the source
table, so you wrap it in a Do Until rsSrc.EOF thing and away you go.