Home / Guy usa free chat america / Updating latge recordsets takes long time in ms access database

Updating latge recordsets takes long time in ms access database

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.


  1. Sharing an entire database over the network has been identified by Microsoft. blocks for faster sequential access, and the table statistics are updated. An inefficient query may take seconds to execute in a single user application, but may. a user from keeping the database open for long periods of time with no activity.

  2. Jun 17, 2016. I need to update the currency exchange rates in an Access 2010 tab many. function would run 120 times for each overall currency update process. A SQL UPDATE statement will be faster 1 ms vs 10 ms for example but in the. Dim db As Database Dim rs As Recordset Dim rs1 As Recordset Dim qdf.

  3. May 6, 2016. I just stumbled upon a blog post of Microsoft Access MVP Daniel Pineault about different. Recordset Dim lRecCount As Long Set rs = db.

  4. Feb 11, 2010. Of course not, dude – because you're pimping Microsoft Access. a better long-term career as a database administrator or developer on huge projects. data access, and database, is more than I would take on personally. do some trivial vba to dynamically change form recordsets so they don't open.

  5. Updating large recordsets takes long time in ms access. location to open my recordsets and for executing SQL. database that comes with Access.

  6. Mar 27, 2011. Indexes will speed data retrieval, but will slow down updates. The client pc then has a database containing the other MS Access objects. Also a Dynaset query will take far longer to display – this is normally the default, but. Forms can become quite large at times and speed is of paramount importance.

Leave a Reply

Your email address will not be published. Required fields are marked *