Help executing a function to extract records from a corrupt table

[David Jensen]

Hi,

We have a mission critical database that has a table to has become corrupt.
From looking at the table it appears that there are two records that are the
source of the problem. I have no idea how it happened, but it appears that
the two corrupt records have the data in the fields shifted over one field
from where they should be. Since the table is populated via a form, that
doesn't make sense to me, but seeing is believing. We have tried various
methods to delete the two corrupt records but they will not delete, even
though we can delete other records in the table. Compact and repair does
not work. The KB 295334 - ACC2002: Jet compact Utility crashes on the
table. I think that the solution to recover the records from the table
might be from this article on the Access Web:
http://www.mvps.org/access/tables/tbl0018.htm. I have copied it below. The
problem is that I don't know how to actually execute this code. Here is
what I thought I was supposed to do. I pasted the code, just like it is,
into a new module. We changed the following lines . . .

Set OldRes = db.OpenRecordset("tbl_Reservations")
Set NewRes = db.OpenRecordset("tbl_New_Res")

.. . . to correspond to the actual corrupt table and desired new table.

We are unsure of what to do with the following line. . .

NewRes![ResID] = OldRes![ResID]

is [ResID] supposed to correspond to existing fields in the table, or is
this function going to create those fields?

When we tried to compile the code, we got the following message: "Invalid
procedure call or argument".

Would someone please elaborate on the particulars of how to actually execute
this code and what we need to do to make it work (the more detail, the
greater our already great appreciation, as we are clearly not as VB literate
as we should be). The frustration of not knowing what to do with this code
is maddening. I am thankful for the helpful individuals on this newsgroup,
however, as you've been life savers before.

Thanks so much.

--
David Jensen
Change the xyz in my email address to MJ for my real email address. Thanks.

________________________________________________________________

Below is the code from http://www.mvps.org/access/tables/tbl0018.htm
(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 ***********

 

[Joseph Meehan]

David Jensen :

You likely now know that a backup of any critical data is .. well
critical.

I have a couple of ideas that may work. first create a new database and
see if you import the data to the new database. Second, if that first
non-distractive idea does not work, try deleting the two defective records
along with the record directly before and after.

Remember to try that delete on a copy of the file!

Best of luck.

 

[Douglas J. Steele]

First, change the declarations from

Dim OldRes As Recordset
Dim NewRes As Recordset

to

Dim OldRes As DAO.Recordset
Dim NewRes As DAO.Recordset

just to make sure you're actually get DAO recordsets. (Both the ADO and DAO
models have recordset objects in them, and if you've got references set to
both, you'll usually find that ADO takes precedence. That article likely
predates the introduction of ADO)

From what I can tell, ResID is supposed to be the name of an existing field
in the table.

When you get an error during compile, what line of code gets highlighted?

Once you've got the code compiling, all you have to do is type Call CopyRes
in the Immediate window (Ctrl-G) to run the code. No reason to even save the
module (unless you think you might need it again)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

We have a mission critical database that has a table to has become
corrupt. From looking at the table it appears that there are two records
that are the source of the problem. I have no idea how it happened, but
it appears that the two corrupt records have the data in the fields
shifted over one field from where they should be. Since the table is
populated via a form, that doesn't make sense to me, but seeing is
believing. We have tried various methods to delete the two corrupt
records but they will not delete, even though we can delete other records
in the table. Compact and repair does not work. The KB 295334 - ACC2002:
Jet compact Utility crashes on the table. I think that the solution to
recover the records from the table might be from this article on the
Access Web: http://www.mvps.org/access/tables/tbl0018.htm. I have copied
it below. The problem is that I don't know how to actually execute this
code. Here is what I thought I was supposed to do. I pasted the code,
just like it is, into a new module. We changed the following lines . . .

Set OldRes = db.OpenRecordset("tbl_Reservations")
Set NewRes = db.OpenRecordset("tbl_New_Res")

. . . to correspond to the actual corrupt table and desired new table.

We are unsure of what to do with the following line. . .

NewRes![ResID] = OldRes![ResID]

is [ResID] supposed to correspond to existing fields in the table, or is
this function going to create those fields?

When we tried to compile the code, we got the following message: "Invalid
procedure call or argument".

Would someone please elaborate on the particulars of how to actually
execute this code and what we need to do to make it work (the more detail,
the greater our already great appreciation, as we are clearly not as VB
literate as we should be). The frustration of not knowing what to do with
this code is maddening. I am thankful for the helpful individuals on this
newsgroup, however, as you've been life savers before.

Thanks so much.

--
David Jensen
Change the xyz in my email address to MJ for my real email address.
Thanks.

________________________________________________________________

Below is the code from http://www.mvps.org/access/tables/tbl0018.htm
(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 ***********

 

[John Vinson]

Hi,

We have a mission critical database that has a table to has become corrupt.

There are a number of resources at Tony Toews' excellent FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

The problem is that I don't know how to actually execute this code. Here is
what I thought I was supposed to do. I pasted the code, just like it is,
into a new module. We changed the following lines . . .

Set OldRes = db.OpenRecordset("tbl_Reservations")
Set NewRes = db.OpenRecordset("tbl_New_Res")

. . . to correspond to the actual corrupt table and desired new table.

We are unsure of what to do with the following line. . .

NewRes![ResID] = OldRes![ResID]

is [ResID] supposed to correspond to existing fields in the table, or is
this function going to create those fields?

You will need to put one of these lines in for each field in each
table. Presumably the fielnames are the same between the two tables.

When we tried to compile the code, we got the following message: "Invalid
procedure call or argument".

Which line was highlighted?

Would someone please elaborate on the particulars of how to actually execute
this code and what we need to do to make it work (the more detail, the
greater our already great appreciation, as we are clearly not as VB literate
as we should be). The frustration of not knowing what to do with this code
is maddening. I am thankful for the helpful individuals on this newsgroup,
however, as you've been life savers before.

Another approach - which might be easier - uses one or more Append
queries. I presume you now have two .mdb files, the corrupt one and a
new one, with tbl_Reservations in the old database and tbl_New_Res in
the new one? In the new database, use File... Get External Data...
Link to connect to the damaged database's copy of tbl_Reservations.
Then create an Append query based on this linked table, appending into
tbl_New_Res. Use a criterion on the Primary Key field of
tblReservations which selects a range of records that does NOT include
the corrupt records; if there are two corrupt records, you might need
to run this query three times. For instance, if records 123 and 841
are corrupt, you could use criteria

= 1 AND <= 122
= 124 AND <= 840
= 842

Do NOT include the id's of the damaged records anywhere in the
criteria - you don't want the query to even GLANCE at these records,
much less read them!

Run these queries to append the undamaged records. Then compact and
repair the new database.

Good luck!

John W. Vinson[MVP]

 

[David Jensen]

Fortunately, the database is backed up nightly. We were trying to avoid
recreating a days worth of data entry.

Thanks for your suggestions We'll see if anything works.

David