Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
If you mean create a temporary table, update from that then then delete the temporary table I'd like to avoid that as the Access database will bloat quickly. In the meantime I have started building the inefficient method as I know how to do this.
I have never done this before but it seems logical.
One potential down side to this would be limited error handling because if the update query fails for some reason I'd have to troubleshoot to figure out what the problem is.
Based on all of the helpful feedback that I have received I have pieced together the follow code.
It may not be pretty (and needs some error handling) but it does work.
And you get to drop breakpoints in there and make sure you've got it right A SQL UPDATE statement will be faster (1 ms vs 10 ms for example) but in the timeframes you can't notice that signal for the noise. Here's one where I set up two recordsets and append the results of one recordset to the second. Are you saying there is code in github.com/Cactus Data/VBA.I suspect that you are correct in that it will still only take a few seconds to update all of the currencies so maybe it's not a big deal...Bloating may not be much if you clear and write to the same table - or use an external file.But I of course would have to check to ensure there is a recordset before attempting to update.Would I have to convert the rate from a variant to the Double data type (this is what the field is set for) before attempting to update or will it accept a variant?Always best to convert to what the field will be in the end It's stored as a double, you may as well force the issue with CDbl() rather than let Access handle it and maybe get it wrong. Ok, then you just need a single loop of iteration your dictionary.Set the recordset, do the update, close the recordset 120 keys in the dictionary?The down side is with 120 currencies to update function would run 120 times for each overall currency update process.Not very efficient but maybe it doesn't matter in the grand scheme of things.I had initially wanted to convert it to local time before storing but thought it better to store it as-is with each currency rate and calculate local time on the fly when required. As suspected though, after some testing I noticed that the database file increases in size by about 17k. FIND method for the key to the row to be updated, then use rs. Update to change the row The Access 2003 help claims that you are better off to execute new SQL than do . Filter Using the Find methods with Microsoft Jet-connected ODBC-accessed recordsets can be inefficient.Not a lot of bloat in the grand scheme of things, especially when the database is going to increase in size anyway due to normal data entry so probably nothing to worry about. You may find that rephrasing your criteria to locate a specific record is faster, especially when working with large recordsets.