PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2017 [RESOLVED] Bulk copy table from MySQL to Access accd-VBForums
Results 1 to 3 of 3

Thread: [RESOLVED] Bulk copy table from MySQL to Access accd

  1. #1

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,171

    Resolved [RESOLVED] Bulk copy table from MySQL to Access accd

    i have a Data table which get loaded from MySQL server , i want to upload this data table onto access data base table
    what is the best option please.
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,048

    Re: Bulk copy table from MySQL to Access accd

    You can call Fill on a data adapter to execute the SelectCommand and retrieve data from MySQL into a DataTable and then call Update to execute the InsertCommand to insert the records into Access. The trick is that you need to set the AcceptChangesOnFill property to False so that AcceptChanges is not called on the DataTable and all RowStates remain Added rather than being set to Unchanged.

    You can choose to use a single data adapter or a pair. You need to use an OleDbDataAdapter for Access so you can use that for MySQL too. In that case, you simply have to use two different OleDbConnection objects for the SelectCommand and InsertCommand, e.g.
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Dim sourceConnection As New OleDbConnection("MySQL cnnection string here")
    4. Dim selectCommand As New OleDbCommand("SQL SELECT statement here", sourceConnection)
    5. Dim destinationConnection As New OleDbConnection("Access connection string here")
    6. Dim insertCommand As New OleDbCommand("SQL INSERT statement here", destinationConnection)
    7.  
    8. 'Add parameters to insertCommand here, e.g.
    9. insertCommand.Parameters.Add("@ColumnName", OleDbType.VarChar, 50, "ColumnName")
    10.  
    11. Dim adapter As New OleDbDataAdapter With {.SelectCommand = selectCommand,
    12.                                           .InsertCommand = insertCommand,
    13.                                           .AcceptChangesDuringFill = False}
    14.  
    15. adapter.Fill(table)
    16. adapter.Update(table)
    Alternatively, you can use two different data adapters and use Connector/Net for MySQL, e.g.
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Dim sourceConnection As New MySqlConnection("MySQL cnnection string here")
    4. Dim selectCommand As New MySqlCommand("SQL SELECT statement here", sourceConnection)
    5. Dim destinationConnection As New OleDbConnection("Access connection string here")
    6. Dim insertCommand As New OleDbCommand("SQL INSERT statement here", destinationConnection)
    7.  
    8. 'Add parameters to insertCommand here, e.g.
    9. insertCommand.Parameters.Add("@ColumnName", OleDbType.VarChar, 50, "ColumnName")
    10.  
    11. Dim sourceAdapter As New MySqlDataAdapter With {.SelectCommand = selectCommand,
    12.                                                 .AcceptChangesDuringFill = False}
    13. Dim destinationAdapter As New OleDbDataAdapter With {.InsertCommand = insertCommand}
    14.  
    15. sourceAdapter.Fill(table)
    16. destinationAdapter.Update(table)
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,171

    Re: Bulk copy table from MySQL to Access accd

    Superb and thanks a lot sir
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

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