March 17th, 2012, 04:47 PM
Fastest way to save data from a DataGridView to a MS Access table if table has no ID
I'm working on an application that will connect to a MS Access database and will let the user run any SQL query he wants. It will also have a DataGridView that will show query results and if these results are the contents of a table, he should be able to modify it's contents.
What I'm doing right now is clear the whole table and insert the new rows in a transaction, inserting record by record (in the Jet engine you can't run many queries at once), but when there are around 50,000 it takes forever to complete.
If the table has a primary key I think I could easily get the modified records in the DataTable bound to the DataGridView and update only these records, but when there is no ID, I can't think of a way of doing it without redoing the whole table. Remember the user is free to run any query he wants and therefore create any table he wants.
I just tested opening a 80,000 records table with no ID with MS Access, and I could make and save changes to the table with virtually no delay. So there must be another way to do this. Any idea how does MS Access do this?