ADO copy record

[vin]

Hi,

I have two recordset with identical fields.

I need to copy row from one to another.

Can you please, advise a fast and efficient method for doing that.

I am aware that I can loop throug fields collection and copy all field values one by one but is there anyting faster and smarter.

Thank you for your attention.

[coolbiz]

I think that is the best way to do so. We'll see if anyone else has other opinion.

-Cool Bizs

[DebbieInFlorida]

Hello, that is exactly what i want to do, and you said you had the possible answer, but you posted nothing????? Can you post it please, if you have an idea??
Thanks

[lsmeteor]

we had a bit of problems copying recordsets so I looked on this forum for a solution and other resources.

I haven't used the solution we found myself, but for what it is worth, I have attached a zip file containing a sample project and explanation.

Enjoy.
hth

[coolbiz]

Debbie,

I think you are referring to me right? I was pointing to the way that VIN suggested, looping through the fields and copying the data.

-Cool Bizs

[M Owen]

Vin,

1st off do these 2 recordsets point to 2 different tables? Also what kind of recordsets are they? If one is dynamic then you "could" add rows from one to the other (depending on the underlying table being queried from by the recordset) ...

[DebbieInFlorida]

Thx guys, but isometeor , that is not what I am looking for, that code seems to just copy the entire recordset. What I want is to just copy certain records. Vin, I think what you are talking about is what I want. I am adding new records to one recordset as I loop thru my original recordset, and want to copy the entire row from the original. My reason for this is to create a new table for any matched records, once the match changes, the new recordset is processed, then deleted, openned again and I move on to the next matching records.
Thank you

[LittleJonny]

The most efficient way would be to use a Update or Insert SQL command.

Update Tablename set fieldname1 = value1 , fieldname2 = value2 , ... where condition.

As in the new row you have 2 quick options Select into an Insert.

Let me know if you need more examples.

LJ

[vin]

What I wanted was to copy fields from one recordset to another. The recordsets are identical and they are disconnected. I do not want to modify the tables in the database, but just the recordsets in memory Currently I do what CoolBiz and I thought from the very beginning. Loop through all fields and copy them. It actually works pretty fine.

[Thread1]

Yeah that's the manual way of manipulating records.. But as M Owen said, if the two record sources are in the same database then it would be much more faster and more efficient to just use SQL statement.. Just a reminder though

[vin]

Dear Thread1,

I quite agree that SQL statements are efficient way to manipulate data when you have a powerfull SQL server at hand.
However I do not refer to tables. I refer to disconnected recordsets, fabricated ones, i.e. no database, no tables. Just a recordset.

[M Owen]

Vin,

Then I don't see a problem ... Since you have to traverse 1 of the recordsets to determine which record(s) to copy the other recordset should be updateable with an AddNew, poke field values across, and then an Update call ... Since these are disconnected recordsets you don't have to worry about affecting the original underlying table(s). You will have to make sure that the TYPE of recordset is of a Dynamic flavor because once the recordset is created/opened the CursorType cannot be changed ...

- Mike

[coolbiz]

I think there is a little confusion here.

Vin started the post asking for a better (more optimized) method to copy a row from one recordset to another. Later, Vin explained that the recordsets are disconnected type and does not want to change anything on the DB itself.

Using SQL statement is out of the question. So Vin original idea is the best way unless ADO Recordset exposes a method to copy a row from one RS to another which I don't think it does.

Of course I could be wrong about looping through the fields to copy the data is the best method (in this situation) and hopefully if someone has an better idea, please post it up.

-Cool Bizs

[Thread1]

Ah ok!.. Be specific.. A disconnected recordset can be initialy populated with records coming from the database, I'm sure you know that .. So now, I have agreed looping through the recordset though

[M Owen]

Cool,

If Vin were using say Adapi then I'd have a quick way of doing what he/she wants ... Since only a recordset is defined NOT the type of recordset (ADO, DAO, RDO, etc ...) I cannot say with any certainty that the recordset in question can do a "batch add". An Adapi recordset can. You create a buffer pump in your records to add, make the call and it does the rest ...