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

Recommended Answers

All 2 Replies

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

In

accDataAdapter.InsertCommand = accCommandBuilder.GetInsertCommand()

i have the follw error:
"Additional Information: You must initialize the DataAdapter.SelectCommand properties."
What should I do?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.