searching for corrupted data

[Jaybird]

I'm trying to append records from one table to another in order to identify
corrupted records and so that I can use the data in another aplication, but I
can't seem to find a method that works. I've copied and pasted, I've
appended into an empty version of the table, I've looked for corrupted
records manually. Allen Browne, in an earlier post, suggested that the
poster use Norm Chezem's code for moving the records from one table to
another, identifying corrupt data. Here is his code as posted onto 'The
Access Web' site:

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

I went ahead and disambiguated the recordsets to ADO.Recordset, but now it
pukes on this line:

OldRes.MoveNext 'Skip this corrupt row

The message box appears, telling me that it has found the error, but then a
Run Time Error '91' occurs. 'Object variable or with block variable not set'
I know enough about VBA to cause trouble, so I'm having trouble resolving
this issue. If anyone can tell me what I'm doing wrong, I'd appreciate it.

 

[Andy Hull]

Hi

Have you only disambiguated the recordset bits or made other changes?

This is just a wild guess but it sounds like you may have used a WITH and
END WITH in your code and that when a corrupt row is found, Access gets
confused because to report the error it jumps out of the WITH block.

You could try removing the WITH and END WITH and using the full recordset
notation instead.

Failing that, can you post the actual code?

Andy Hull

 

[Jaybird]

Amazing that 'disambiguate' is a real word in this context... Anyhow, here's
the modified code:

Function CopyRes()
Dim db As Database
Dim OldRes As DAO.Recordset
Dim NewRes As DAO.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>" & Error$
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function

All I did was to make
Dim OldRes As Recordset
Dim NewRes As Recordset
into
Dim OldRes As DAO.Recordset
Dim NewRes As DAO.Recordset

I still get the error. BTW, I don't know what you mean my using the full
recordset notation. Also, I made sure that my references included DAO 3.6

 

[Andy Hull]

Hi

Unfortunately I can't see what the problem could be.

Will need to have a think.

When I said full recordset notation it was just my way of saying to not use
a WITH statement.

If you have several commands to perform on a recordset you can do this...

WITH NewRes
.AddNew
.Fields![Order Number] = OldRes![Order Number]
'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
.Update
END WITH

so above, you didn't have to type NewRes every time between WITH and END WITH

I thought that might be the problem but it isn't!

Will get back to you if I have any ideas

Good Luck

 

[Jaybird]

Thanks. I was wondering if it might be because of missing references or
something.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hi

Unfortunately I can't see what the problem could be.

Will need to have a think.

When I said full recordset notation it was just my way of saying to not use
a WITH statement.

If you have several commands to perform on a recordset you can do this...

WITH NewRes
.AddNew
.Fields![Order Number] = OldRes![Order Number]
'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
.Update
END WITH

so above, you didn't have to type NewRes every time between WITH and END WITH

I thought that might be the problem but it isn't!

Will get back to you if I have any ideas

Good Luck

Amazing that 'disambiguate' is a real word in this context... Anyhow, here's
the modified code:

Function CopyRes()
Dim db As Database
Dim OldRes As DAO.Recordset
Dim NewRes As DAO.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>" & Error$
OldRes.MoveNext 'Skip this corrupt row
Resume Addit 'Continue at Addit
End Function

All I did was to make
Dim OldRes As Recordset
Dim NewRes As Recordset
into
Dim OldRes As DAO.Recordset
Dim NewRes As DAO.Recordset

I still get the error. BTW, I don't know what you mean my using the full
recordset notation. Also, I made sure that my references included DAO 3.6