BIG NOTE -

I ran my database update code last night and finally hit the sack. This morning I checked the stats and my update of 65000 records had taken about two hours. Roughly 10 updates a second.

This morning I noticed that one of the records had not updated correctly. I lazily blanked out 95% of the update code (case statement) just leaving the one records type I needed to do the update on.

Running the code again, I expected the counter to fly up to the record that I needed to change - and then plod at ten records a second.

But no, what happened was that even cycling through to next record - without doing anything - still took 0.1 of a second.

So, I modified the SQL to pick out ONLY the lines I needed to update. Pressed the button, and 3500 records updated in 35 seconds. 10mSecs per update as oposed to 100msecs.

So it looks like it was the physical size of the recordset that was causing my speed hangups. It wasn't the update, just the .movenext statement.

So the tip looks like being .. chop up recordsets where you can and update in small chunks rather than large chunks.

FYI. The 65000 records occupied 15Meg of the Access db size.

If I ever get around to it I will do a speed compare using an SQL to do the update rather than using ADO.