Copy whole row

[John J.]

In VBA is there some special recordset feature that lets you copy a whole
row in stead of having to specify every field explicitly?

Thank you.
John

 

[vbasean]

you can loop through each field in a recordset row using the 'For Each' method:

Dim f as Field
Dim rs as Recordset
Set rs = CurrentDb.Openrecordset("YourRecordset")
For Each f in rs.Fields
'Do this with the field, i.e. copy etcetera
Next

But... the great 'normalist' asked, Why are you copying rows?

 

[John J.]

Thanks!
Why copying rows? I'm writing a generic copy routine that can copy a table's
content into an empty one in case of corruption.
John

 

[vbasean]

your situation could benefit from two different approaches:
backups
replication

 

[John W. Vinson]

Thanks!
Why copying rows? I'm writing a generic copy routine that can copy a table's
content into an empty one in case of corruption.
John

Since corruption is most likely to involve VBA code, or secondarily system
tables, or thirdly Memo fields... you would really be best off keeping good
backups *external to Access*, backing up the entire database file.

 

[John J.]

John, Chris,

I do make backups, but I read that corruption can show itself days or even
months later than the corruption took place. I see copying the tables
content as a last resort. I started with this code I found on the internet.

John

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("Tabel1")
Set NewRes = db.OpenRecordset("Test")
RecCount = 0
OldRes.MoveFirst
Do While Not OldRes.EOF
Addit:
NewRes.AddNew
NewRes![ID_test] = OldRes![ID_test]
'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 ***********