Copy tables using recordset

[MatsHulten]

Can someone help me out with this one!

I need to copy a table from one DB to another using nothing but ADO. Getting the data into a recordset is not a problem (select * from table), however I would like a more simple solution to updating the other DB than doing an insert for every record. (The other table exists already, but is empty.)

Any hints?
-Mats

 

[Guest_imported]

Create view in second Database, using first database first table.Then directly select from this view..!!

 

[DamoOz]

Can explain a little more as I have a similar problem.

Can you copy from one recordset to another even if they are from different connections.

DamoOz

 

[MatsHulten]

Thank you Damo, you have summarised the problem nicely.
Snoopy: I need to do this using nothing but ADO, the solution you suggested would certainly work if either of the DB supported views, but neither do. All I've got is an ODBC connection to a SqlBase system, and a regular Access DB.

-Mats

 

[steveblum]

I think based on what you are trying to do, you don't have a choice but to go through every record in the first recordset using a loop (rs.movenext until rs.eof = true). Either do an insert into the other table or a dynamic recordset pointing to the second table with a .addnew and .update approach. The first is probably easier to implement, and should run as fast or faster than the second since dynamic cursors are slow.

 

[DamoOz]

Mats

I think we have a very similar problem - you have posted a reply to my question ("a quicker way to import CSV file in ADO) in the VB - Database forum.

We have to find a solution to this!! There must be a quicker way to load a table form a table in another database or CSV file etc. than using .addnew and .update methods for each record. I cannot do it this way in a particular app as the file has far too many records and is too slow. What we really need is an ADO version for the VBA code ......
DoCmd.TransferDatabase acImport, .......... or
DoCmd.TransferDatabase acLink.......

From an Access module using the above code it only takes about 10 seconds to import my table but using ADO and .addnew it takes about 5 minutes which is unacceptable.

Can someone please help Mats and I as there must be an alternative in ADO.

Damo

 

[DamoOz]

Hey Mats..............Maybe nobody knows!!!

Can I even copy from one recordset into another recordset if they are on different connections???.

 

[MatsHulten]

To be honest I don't think it is possible. It is easy to see the recordset as an array, populated with data from a database. (And some recorsets are)
However the general idea of a recordset is that it should be an active subset of the database, with a live connection to it.
If you sever that connection, you can persist the data, but you cannot transfer the data to another connection, without transfering it to another recordset first.
As far as I have seen there exits no native method in ADO for this.

-Mats