How to copy data from one DB to another through ADO

[jsmith]

I need to copy data from one DB to another using ADO. I need to see if the data exist then do update and if not insert it.

Any ideas on a code ?
thanx a lot

[JHausmann]

I need to copy data from one DB to another using ADO. I need to see if the data exist then do update and if not insert it.

Any ideas on a code ?
thanx a lot

You'll need to have a unique key that's the same in both tables.

1) open a recordset in source table, reading all the records
2) open a recordset in the destination table finding the unique record returned in step 1
3) if recordset.eof = false, update the fields you want to update ELSE insert the record.
4) close the destination recordset.
5) loop items 1- 4 until you reach the end of the source table.

[jsmith]

I already have the primary keys in both tables, and have conection to both database.
Can you give me a CODE sample for step 3 and 4
Thanx A lot

[JHausmann]

dim SQL as string
dim rs  as ADODB.Recordset
dim rs2 as ADODB.Recordset

SQL="select * from table where condition"
rs.Open SQL, your_connection
if rs.EOF = False Then
   Do While Not rs.EOF 
'following assumes a numeric key 
      SQL= "select * from table where unique_key=" & rs.Fields("unique_key") 
      rs2.Open SQL, your_other_connection
      If rs2.eof Then 'no records retrived
         'insert routines here
      Else
         'update routines here
      End If
      rs2.close
      set rs2 = Nothing
      rs.MoveNext
   Loop
End If
rs.close
set rs = Nothing

[jsmith]

thank you