copy table from database to another database vb6

hii

i want to copy table from database1 to another database2 and i use for loop but the process very slowly for 1million rows take more than one hours, the example for loop:

Code:

---

Dim i As Integer
i = 0
Adodc1.Recordset.MoveFirst
For i = 1 To Adodc1.Recordset.RecordCount
Adodc2.Recordset.AddNew
Adodc2.Recordset.Fields(0) = Adodc1.Recordset.Fields(0)
Adodc2.Recordset.Fields(1) = Adodc1.Recordset.Fields(1)
Adodc2.Recordset.Update
Next

---

so if i want copy data from one table to anther table with different database and how use insert below i need modify code below :

Code:

---

' To create table in database 2
Const strConnection As String = "Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=database 2"
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
strName = InputBox("Enter table name:", "Table Name")
cnn.ConnectionString = strConnection
cnn.Open
cmd.ActiveConnection = cnn
cmd.CommandText = "create table " & strName & " ( 1number VARCHAR (40), 2number VARCHAR (40) )"
cmd.Execute
'.................................................................................................
'To select data from database 1
Adodc1.ConnectionString = " Provider=MSDASQL.1;Password=123;Persist Security Info=True;User ID=support;Data Source=database 1"
Adodc1.RecordSource = "select 1number,2number where 1number = '1111'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
'...............................................................
'To select data from database 2
Adodc2.ConnectionString = " Provider=MSDASQL.1;Persist Security Info=False;User ID=123;Data Source=database 2"
Adodc2.RecordSource = "select * from " & strName & ""
Adodc2.Refresh
Set DataGrid2.DataSource = Adodc2

---

how can copy 1million rows form on table to anther with different database in less than 3 min.

:) thank you

Connect to the database before the new source table.
(linked table)

after try this

cnn.execute "select * into newtablename from oldtablename"

thank you for replay

the problem is how can use insert with two different database with different ConnectionString

regards

to make a link your tables like this example
and try previous sample

Code:

---

Dim adoCn As ADODB.Connection
Dim adoCat As New ADOX.Catalog
Dim adoTbl As New ADOX.Table

Private Sub Command1_Click()

'Create Link...
Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = adoCn

Set adoTbl.ParentCatalog = adoCat
adoTbl.Name = "LinkTable"

adoTbl.Properties("Jet OLEDB:Link Datasource") = App.Path & "\myLinkDatabase.mdb"
adoTbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=myLinkPassword"
adoTbl.Properties("Jet OLEDB:Remote Table Name") = "LinkDatabaseTable"
adoTbl.Properties("Jet OLEDB:Create Link") = True

'Append the table to the tables collection
adoCat.Tables.Append adoTbl

Form1.Caption = "Link Created..."

End Sub

Private Sub Command2_Click()

'Refresh Link...
Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = adoCn

Set adoTbl.ParentCatalog = adoCat

For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "LINK" And (adoTbl.Name = "LinkTable") Then
        adoTbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=myLinkPassword"
        adoTbl.Properties("Jet OLEDB:Link Datasource") = App.Path & "\myLinkDatabase.mdb"
    End If
Next

Form1.Caption = "Link Refreshed..."

End Sub

Private Sub Form_Load()
 
strCn = App.Path & "\myDatabase.mdb"
Set adoCn = New ADODB.Connection
With adoCn
    .Provider = "Microsoft.JET.OLEDB.4.0;" & _
                    "Jet OLEDB:Database Password=myPassword"
    .Open strCn
End With

End Sub

---

source:http://support.microsoft.com/kb/240222

Er, aren't these SQL Server databases?

if you have these tables ina data base I would suggest easiest option is write a transfer in or out in one of the databases and invoke the transfer process from vb6 if you must use vb6.

I am sure the db version will be straight forward enough