-
Jan 4th, 2012, 03:01 AM
#1
Thread Starter
New Member
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
-
Jan 4th, 2012, 04:36 AM
#2
New Member
Re: copy table from database to another database vb6
Connect to the database before the new source table.
(linked table)
after try this
cnn.execute "select * into newtablename from oldtablename"
-
Jan 4th, 2012, 05:08 AM
#3
Thread Starter
New Member
Re: copy table from database to another database vb6
thank you for replay
the problem is how can use insert with two different database with different ConnectionString
regards
-
Jan 4th, 2012, 06:25 AM
#4
New Member
Re: copy table from database to another database vb6
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
-
Jan 4th, 2012, 01:54 PM
#5
Re: copy table from database to another database vb6
Er, aren't these SQL Server databases?
-
Jan 4th, 2012, 05:28 PM
#6
Frenzied Member
Re: copy table from database to another database vb6
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|