How to?: Use a disconnected recordset to populate a table in another DB

How to?: Use a disconnected recordset to populate a table in another DB

Post by cpar » Wed, 16 Oct 2002 05:04:33



Using a select stmt. (select * into..) I create a recordset (oRS) and
disconnect it. I want to connect to another DB and insert the rows
from oRS into an empty table with the same definition as the one from
which I read.

Do I have to create an insert stmt and use a command object while
iterating through oRS, or is there a way I can update from oRS?

thanks guys!

 
 
 

How to?: Use a disconnected recordset to populate a table in another DB

Post by Val Mazu » Wed, 16 Oct 2002 21:02:24


Hi,

One way is to create some sort of INSERT statement and iterate through
records. Another way is to save your recordset into XML file/stream and
modify status of records as new one. Load XML back into recordset, reconnect
recordset to another database and call UpdateBatch. If you need example how
to do that, let me know by email, and I will e-mail it to you. It is too big
to post it here

--
Val Mazur
Microsoft MVP


Quote:> Using a select stmt. (select * into..) I create a recordset (oRS) and
> disconnect it. I want to connect to another DB and insert the rows
> from oRS into an empty table with the same definition as the one from
> which I read.

> Do I have to create an insert stmt and use a command object while
> iterating through oRS, or is there a way I can update from oRS?

> thanks guys!


 
 
 

How to?: Use a disconnected recordset to populate a table in another DB

Post by Paul Clemen » Wed, 16 Oct 2002 22:27:06



Using a select stmt. (select * into..) I create a recordset (oRS) and
disconnect it. I want to connect to another DB and insert the rows
from oRS into an empty table with the same definition as the one from
which I read.

Do I have to create an insert stmt and use a command object while
iterating through oRS, or is there a way I can update from oRS?

I don't believe you can use a disconnected Recordset in this fashion. UpdateBatch will only work
with the orginal datasource from which the Recordset was generated.

If you simply want to export data from a table in one database to another, you may be able to do
this with straight SQL if using Access or one of the ISAM database types (e.g. dBase, Paradox,
etc.).


Microsoft MVP (Visual Basic)

 
 
 

1. Populate a disconnected recordset

Don't know if this is doable, but here goes....

I've constructed a disconnected recordset;

     Set rsDisc = New ADODB.Recordset
     With rsDisc
          .Fields.Append "DetailNum", adChar, 50, adFldUpdatable
          .Fields.Append "Sono", adChar, 50, adFldUpdatable
          .Fields.Append "Quantity", adDouble, , adFldUpdatable
          .Fields.Append "Price", adDouble, , adFldUpdatable
          .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
      .Open
   End With

Then opened a table and populated second recordset;

               Set cn = New ADODB.Connection
              cn.ConnectionString=
"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\Db\Accucom.mdb;Persist
Security Info=False"
              cn.CursorLocation = adUseClient
              cn.Open
               Set rs = cn.Execute("select * from JobDetail")

Now I want to populate first recordset with the contents of the second;

            Do Until rs.EOF
                    rsDisc!DetailNum = rs!DetailNum
                    rsDisc!Sono = rs!Sono
                    rsDisc!Quantity = rs!Quantity
                    rsDisc!Price = rs!Price
            Loop

And then I get the 3021 error 'Either EOF or BOF is true or the current
record has been deleted. Requested operation requires a current record.'
So guess the trick is to hold the two recordsets open in memory (assuming
that this is possible). But how? TIA

2. All===Writers!!!!....

3. Sproc using temp tables & disconnected recordset

4. S0S - Sql query

5. Copying Tables Using Disconnected Recordsets

6. Programmatically positioning Columns

7. Using table data types to populate a real table

8. VR in line 1 of an MD item?

9. Updating db by connecting a disconnected recordset in ADO

10. Can't get recordset to populate datagrid using Access

11. Populating ComboBox from a Recordset using DAO

12. Populating Data Grid using ADO recordset