Copy ADO Recordset

[JimS]

I have a subset of records I want to "clone" into the same table. Originally,
I did it with SQL, and that worked fine, but I've had to change some of the
fields and the SQL is becoming confusing for anyone else who follows to
understand, so I'm doing it with a pair of ADO recordsets...

Initially, what I want to do is copy EVERY field from each record in the one
recordset (rst2) into the new record in the other recordset (rst1). Ideally,
I should be able to code something like
while not rst2.eof
rst1.addnew
rst1.everyfield=rst2.everyfield ' This is the property I don't
know.....
...etc...
rst1.update
rst2.movenext
end while

Is there a property like "everyfield", or must I exlicitly assign every
field...(for each...)

 

[Dirk Goldgar]

I have a subset of records I want to "clone" into the same table.
Originally,
I did it with SQL, and that worked fine, but I've had to change some of
the
fields and the SQL is becoming confusing for anyone else who follows to
understand, so I'm doing it with a pair of ADO recordsets...

Initially, what I want to do is copy EVERY field from each record in the
one
recordset (rst2) into the new record in the other recordset (rst1).
Ideally,
I should be able to code something like
while not rst2.eof
rst1.addnew
rst1.everyfield=rst2.everyfield ' This is the property I
don't
know.....
...etc...
rst1.update
rst2.movenext
end while

Is there a property like "everyfield", or must I exlicitly assign every
field...(for each...)

There's no "EveryField" property. Do the fields have the same names? If
so, then you can easily use a For Each loop, as you suggested:

Dim fld As ADODB.Field

While Not rst2.EOF

rst1.AddNew

For Each fld In rst2.Fields
rst1.Fields(fld.Name) = fld.Value
Next fld

rst1.Update

rst2.MoveNext

End While

If one of the fields is an autonumber, you'll have to exclude that field
from processing.

 

[JimS]

Thanks, Dirk. I appreciate your time.
--
Jim

There's no "EveryField" property. Do the fields have the same names? If
so, then you can easily use a For Each loop, as you suggested:

Dim fld As ADODB.Field

While Not rst2.EOF

rst1.AddNew

For Each fld In rst2.Fields
rst1.Fields(fld.Name) = fld.Value
Next fld

rst1.Update

rst2.MoveNext

End While

If one of the fields is an autonumber, you'll have to exclude that field
from processing.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)