Searching for Corrupt Data

J

Jaybird

This code seems to be looping through the same record over and over. Any
idea what's wrong with it?


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("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![Order Number] = OldRes![Order Number]
'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 "<This is the error!>" & (Chr(13)) & (Chr(13)) & Error$ & (Chr(13))
& (Chr(13)) & NewRes![Order Number], vbOKCancel
If Response = "OK" Then Resume
If Response = "Cancel" Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function
 
J

Jaybird

Thanks. I stink at this. Do you know of a way I can export all of the
fields to my new table without having to explicitly state them like
OldRes![Order Number]. Can't I just say OldRes.Fields = NewRes.Fields? Or
does that ruin the effect of looping throug the recordset?
--
Why are you asking me? I dont know what Im doing!

Jaybird


Sylvain Lafontaine said:
Well, there are no OldRes.MoveNext inside the loop.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Jaybird said:
This code seems to be looping through the same record over and over. Any
idea what's wrong with it?


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("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![Order Number] = OldRes![Order Number]
'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 "<This is the error!>" & (Chr(13)) & (Chr(13)) & Error$ &
(Chr(13))
& (Chr(13)) & NewRes![Order Number], vbOKCancel
If Response = "OK" Then Resume
If Response = "Cancel" Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function
Click to expand...
Click to expand...
 
K

Klatuu

Assuming both tables have the exact same number of fields and the fields are
in the same order, yes.

I would rewrite the Error handler. As written, you have a good chance of
creating an endless loop. The error may not relate to the data in the
current record. If so, it would go to addit, the error would reoccur which
would launch the error handler which would send you to addit, the error would
reoccur which would.....
You get the picture.

Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
Dim lngNdx As Long

On Error GoTo Err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")

OldRes.MoveFirst
Do While Not OldRes.EOF
NewRes.AddNew
For lngNdx = 0 to NewRes.Fields.Count - 1
NewRes.Field(lngNdx) = OldRes.Fields(lngNdx)
Next lngNdx
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

Proc_Exit:

On Error Resume Next

OldRes.Close
NewRes.Close
Set OldRes = Nothing
Set NewRes = Nothing
Set db = Nothing

Exit Function

Err_Proc:
MsgBox "<This is the error!>" & (Chr(13)) & (Chr(13)) & Error$ & (Chr(13))
& (Chr(13)) & NewRes![Order Number], vbOKCancel
If Response = "OK" Then Resume
If Response = "Cancel" Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function


--
Dave Hargis, Microsoft Access MVP


Jaybird said:
Thanks. I stink at this. Do you know of a way I can export all of the
fields to my new table without having to explicitly state them like
OldRes![Order Number]. Can't I just say OldRes.Fields = NewRes.Fields? Or
does that ruin the effect of looping throug the recordset?
--
Why are you asking me? I dont know what Im doing!

Jaybird


Sylvain Lafontaine said:
Well, there are no OldRes.MoveNext inside the loop.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Jaybird said:
This code seems to be looping through the same record over and over. Any
idea what's wrong with it?


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("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![Order Number] = OldRes![Order Number]
'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 "<This is the error!>" & (Chr(13)) & (Chr(13)) & Error$ &
(Chr(13))
& (Chr(13)) & NewRes![Order Number], vbOKCancel
If Response = "OK" Then Resume
If Response = "Cancel" Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function
Click to expand...
Click to expand...
Click to expand...
 
J

Jaybird

Cooool! I like the method. Thanks, Dave! I rewrote it like this:

Option Compare Database

Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
Dim lngNdx As Long

On Error GoTo Err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")

OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
For lngNdx = 0 To NewRes.Fields.Count - 1
NewRes.Fields(lngNdx) = OldRes.Fields(lngNdx)
Next lngNdx
NewRes.Update
RecCount = RecCount + 1
DoEvents
If RecCount Mod 10000 = 0 Then
MsgBox RecCount 'Show progress every 10,000 rows
End If
OldRes.MoveNext
Loop

MsgBox RecCount 'Show total successful record count

Proc_Exit:

On Error Resume Next

OldRes.Close
NewRes.Close
Set OldRes = Nothing
Set NewRes = Nothing
Set db = Nothing

Exit Function

Err_Proc:
Response = MsgBox("This is the error!" & (Chr(13)) & (Chr(13)) & Error$ &
(Chr(13)) & (Chr(13)) & NewRes![Order Number], vbOKCancel)
If Response = vbOK Then Resume
If Response = vbCancel Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit

End Function


The error procedure is as it is because I figured that the Primary Key would
keep duplicate records from being created, but it keeps dying at around 41000
records. I've examined the data that was transferred, but can't see what the
problem is. Access keeps shutting down due to an unexpected error. This is
exactly the same problem that occurs when the table is copied and appended.
 
J

Jaybird

Yeah, I'm guessing that it's something not visible to me just looking at the
data. Do you think I should make this line:

For lngNdx = 0 To NewRes.Fields.Count - 1

Like this?

For lngNdx = 0 To NewRes.Fields.UBound(Count)
--
Why are you asking me? I dont know what Im doing!

Jaybird


Klatuu said:
Looks okay to me.
I am still not comfortable with the addit: thing.
--
Dave Hargis, Microsoft Access MVP


Jaybird said:
Cooool! I like the method. Thanks, Dave! I rewrote it like this:

Option Compare Database

Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
Dim lngNdx As Long

On Error GoTo Err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")

OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
For lngNdx = 0 To NewRes.Fields.Count - 1
NewRes.Fields(lngNdx) = OldRes.Fields(lngNdx)
Next lngNdx
NewRes.Update
RecCount = RecCount + 1
DoEvents
If RecCount Mod 10000 = 0 Then
MsgBox RecCount 'Show progress every 10,000 rows
End If
OldRes.MoveNext
Loop

MsgBox RecCount 'Show total successful record count

Proc_Exit:

On Error Resume Next

OldRes.Close
NewRes.Close
Set OldRes = Nothing
Set NewRes = Nothing
Set db = Nothing

Exit Function

Err_Proc:
Response = MsgBox("This is the error!" & (Chr(13)) & (Chr(13)) & Error$ &
(Chr(13)) & (Chr(13)) & NewRes![Order Number], vbOKCancel)
If Response = vbOK Then Resume
If Response = vbCancel Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit

End Function


The error procedure is as it is because I figured that the Primary Key would
keep duplicate records from being created, but it keeps dying at around 41000
records. I've examined the data that was transferred, but can't see what the
problem is. Access keeps shutting down due to an unexpected error. This is
exactly the same problem that occurs when the table is copied and appended.
Click to expand...
Click to expand...
 
K

Klatuu

No, the Ubound function would not do anything except thow an error. That is
not the part that bothers me. It is continuing to process when an error
occurs. It could be the error has nothing to do with the data and you would
still get hung in an endless loop. If you must do that, then you need to use
the error handler routine to determine what the error is and make a decision
on whether it is safe to continue.
--
Dave Hargis, Microsoft Access MVP


Jaybird said:
Yeah, I'm guessing that it's something not visible to me just looking at the
data. Do you think I should make this line:

For lngNdx = 0 To NewRes.Fields.Count - 1

Like this?

For lngNdx = 0 To NewRes.Fields.UBound(Count)
--
Why are you asking me? I dont know what Im doing!

Jaybird


Klatuu said:
Looks okay to me.
I am still not comfortable with the addit: thing.
--
Dave Hargis, Microsoft Access MVP


Jaybird said:
Cooool! I like the method. Thanks, Dave! I rewrote it like this:

Option Compare Database

Function CopyRes()
Dim db As Database
Dim OldRes As Recordset
Dim NewRes As Recordset
Dim ErrMsg1 As String
Dim RecCount As Long
Dim lngNdx As Long

On Error GoTo Err_Proc
Set db = CurrentDb()
Set OldRes = db.OpenRecordset("Order Entry")
Set NewRes = db.OpenRecordset("Order Entry2")

OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
For lngNdx = 0 To NewRes.Fields.Count - 1
NewRes.Fields(lngNdx) = OldRes.Fields(lngNdx)
Next lngNdx
NewRes.Update
RecCount = RecCount + 1
DoEvents
If RecCount Mod 10000 = 0 Then
MsgBox RecCount 'Show progress every 10,000 rows
End If
OldRes.MoveNext
Loop

MsgBox RecCount 'Show total successful record count

Proc_Exit:

On Error Resume Next

OldRes.Close
NewRes.Close
Set OldRes = Nothing
Set NewRes = Nothing
Set db = Nothing

Exit Function

Err_Proc:
Response = MsgBox("This is the error!" & (Chr(13)) & (Chr(13)) & Error$ &
(Chr(13)) & (Chr(13)) & NewRes![Order Number], vbOKCancel)
If Response = vbOK Then Resume
If Response = vbCancel Then Exit Function
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit

End Function


The error procedure is as it is because I figured that the Primary Key would
keep duplicate records from being created, but it keeps dying at around 41000
records. I've examined the data that was transferred, but can't see what the
problem is. Access keeps shutting down due to an unexpected error. This is
exactly the same problem that occurs when the table is copied and appended.
Click to expand...
Click to expand...
Click to expand...
 
J

Jaybird

Error handling is not my strong suit. If this doesn't handle it:

On Error GoTo Err_Proc

Then I'm screwed.
 
K

Klatuu

An error procedure is like any other. You can do pretty much what you want.
One technique is to determine what the error is and procede based on that.
For example, if you are running a report and the report has do data
available, you will get an error 2501 because the report is canceled.
Usually, you have presented a message box in the No Data event of the report,
so you don't need to tell the user anything else. However, if some other
error occurs, you want to present a message box with that information:

If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical
End If

I know this does not apply directly to you, but if you know which data error
you may get while copying the the fields into place, you could actually show
which field has the bad data and which record it is in:

If Err.Number = ??? 'Whatever error Then
MsgBox "Bad Data in " & OldRes.Fields(lngNdx).Name & " in Record " &
OldRes.AbsolutePosigion
Resume addit
Else
MsgBox ......
End If
 
J

Jaybird

Dave,

After doing the same thing over and over, I think I've found the troublesome
record. Unfortunately, it can't be deleted by normal means. I tried
deleting it manually with no success. I even tried a delete query, but all I
got was this error:

The search key was not found in any record. (Error 3709)

Well, shoot. It never should have been saved because it violates the index
key rule. I think this is the records that's been screwing everything up.
Any ideas?

John
 
K

Klatuu

What happens if you go directly to the table? Have you tried to locate the
record and delete it from there?
 
J

Jaybird

Nope. Can't do a thing with it. I'm getting a hotfix from MS now. I'll let
you know how it turns out.
 
J

Jaybird

Well the hotfix doesn't work on my computer OS. I'll try again in a few
hours. Apparently, there is a known problem with Jet Engine 4.0. I seem to
be a victim. Thanks for all your advice and help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

searching for corrupted data 4
Corrupt Table 1
Q re copying Recorset 2
Winfax from Access 0
tcp/ip rather than named pipes for dsn setup 2
error 462, vba and powerpoint 0
Invalid Object error after refreshing linked table 0
Help needed Writing formula that totals data at end of column 7

Top