Edit Question - KnowledgeHub

Title Body

This is kind of a follow-up to this thread . This is all with .Net 2.0 ; for me, at least. Essentially, Marc (OP from above) tried several different approaches to update an MS Access table with 100,000 records and found that using a DAO connection was roughly 10 - 30x faster than using ADO.Net. I went down virtually the same path (examples below) and came to the same conclusion. I guess I'm just trying to understand why OleDB and ODBC are so much slower and I'd love to hear if anyone has found a better answer than DAO since that post in 2011. I would really prefer to avoid DAO and/or Automation, since they're going to require the client machine to either have Access or the database engine redistributable (or I'm stuck with DAO 3.6 which doesn't support .ACCDB). Original attempt; ~100 seconds for 100,000 records/10 columns: Dim accessDB As New OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ accessPath & ";Persist Security Info=True;") accessDB.Open() Dim accessCommand As OleDb.OleDbCommand = accessDB.CreateCommand Dim accessDataAdapter As New OleDb.OleDbDataAdapter( _ "SELECT * FROM " & tableName, accessDB) Dim accessCommandBuilder As New OleDb.OleDbCommandBuilder(accessDataAdapter) Dim accessDataTable As New DataTable accessDataTable.Load(_Reader, System.Data.LoadOption.Upsert) //This command is what takes 99% of the runtime; loops through each row and runs //the update command that is built by the command builder. The problem seems to //be that you can't change the UpdateBatchSize property with MS Access accessDataAdapter.Update(accessDataTable) Anyway, I thought this was really odd so I tried se

Tags (comma-separated) Cancel