If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy whole row
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 |
#2
|
|||
|
|||
Copy whole row
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? -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "John J." wrote: 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 |
#3
|
|||
|
|||
Copy whole row
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" schreef in bericht news 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? -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "John J." wrote: 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 |
#4
|
|||
|
|||
Copy whole row
your situation could benefit from two different approaches:
backups replication -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "John J." wrote: 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" schreef in bericht news 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? -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "John J." wrote: 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 |
#5
|
|||
|
|||
Copy whole row
On Mon, 27 Oct 2008 22:09:54 +0100, "John J." wrote:
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 W. Vinson [MVP] |
#6
|
|||
|
|||
Copy whole row
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 *********** "John W. Vinson" schreef in bericht ... On Mon, 27 Oct 2008 22:09:54 +0100, "John J." wrote: 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 W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|