copy table from database to another database vb6-VBForums
Results 1 to 6 of 6

Thread: copy table from database to another database vb6

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2012
    Posts
    5

    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

  2. #2
    New Member
    Join Date
    Jan 2012
    Posts
    7

    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"

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2012
    Posts
    5

    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

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    7

    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

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    11,317

    Re: copy table from database to another database vb6

    Er, aren't these SQL Server databases?

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.