Start New Discussion within our Software Development Community

I wonder if anyone has solved this problem already.
I have a fully populated datatable (ado.net datatable) containing about 3000 rows of data with 53 fields.
I want to copy the contents of the data table into an access database table ready prepared and empty which has the identical structure in terms of fields. Although I could loop through the data table row by row and insert each row using a command insert this takes ages (several minutes). Also I dont want to use sql server, bulk copy because I want to keep all logic in vb.net program.

Is there anyway of doing this without a loop. Several days Googling has been fruitless.
thanks
Waldek

Something like this:

Private Sub CopyToAccess(source As DataTable)
    Try
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;"

        Dim accConnection As New OleDb.OleDbConnection(connString)
        Dim selectCommand As String = "SELECT <field1>, <field2> and so on FROM <table>"
        Dim accDataAdapter As New OleDb.OleDbDataAdapter(selectCommand, accConnection)

        Dim accCommandBuilder As New OleDb.OleDbCommandBuilder()
        accDataAdapter.InsertCommand = accCommandBuilder.GetInsertCommand()
        accDataAdapter.UpdateCommand = accCommandBuilder.GetUpdateCommand()

        Dim accDataTable As DataTable = source.Copy()

        ''Just to make sure, set the RowState to added to make sure an Insert is performed'
        'For Each row As DataRow In accDataTable.Rows'
        '    If row.RowState = RowState.Added Or RowSate.UnChanged Then'
        '        row.SetAdded()'
        '    End If'
        'Next'

        accDataAdapter.Update(accDataTable)
    Catch ex As Exception
    End Try
End Sub
This article has been dead for over six months. Start a new discussion instead.