I am converting a table which contains almost 6 million records. Every time I touch anything it takes hours and I would like to find out if anyone has tips on how to make at least the updating part of it go quicker. It is hard to experiment with different ways of accomplishing this when everything takes so long.
- Around 250,000 records are being updated - two fields, both of which are indexed. Would it help much to remove those indexes?
- It is in version 2003 (mdb) while I am using 2010. In one attempt I converted it to 2010 to see if that would help but converting it back took an entire day (in fact I think I gave up on it)
- I inserted a make-table query before the update query which creates a table of only those records which need to be updated, not sure if this helps but it does have the advantage of no longer having to use linked tables in the update query
- The update query had been running for 6 hours when I left for the day!
- This is happening in three separate conversions over a period of weeks so this is only about a third of it.
Would reading/updating the records in VBA make it any faster?
PS Please don't answer questions that haven't been asked here - I have already been thru it all with the users! Any changes I make have to be done in secret and put back the way they were after conversion.