Updating latge recordsets takes long time in ms access database define online dating

Just not sure where to position the transaction statements. A transaction would bail on ALL actions unless they were all good, and I don't think that's what you are aiming for.

updating latge recordsets takes long time in ms access database-43updating latge recordsets takes long time in ms access database-63updating latge recordsets takes long time in ms access database-18

Run SQL ("Insert into temp Billing Clone select billing ID, Job ID, Description, Date, quantity, Rate, account from tbl Billing where job ID = " & Previous. With using the currency code as the criteria only one record should be returned so no need to loop after the rate has been updated.The thing to avoid is a loop where you run Current Db. Except from that, you'll hardly notice any difference. If you pull it from a web service, you could update your data right away - see an example here: Call the CVRAPI using Java Script It's the Json modules that do the dirty work using collections./gustav Until you start dealing with 10's of thousands of records, doing record updating via DAO ! Add New The guts of it are at line 53 Sub 1 second for number of records Gustav, you already pointed me to that code in Call the CVRAPI using Java Script in one of my previous posts and there seems to be some great examples in there but I have already started using this code that seem to be doing the trick: So I was going to pass "key" which is the Currency Code and "po Dict(key)" which is the Currency Rate to a function that opens an SQL record set using "key" as the criteria to find the currency code, then update the currency Rate with "po Dict(key)" and the update.This is called from a command button on my test form but will actually be executed automatically from a timer routine...I am thinking that all of this should be somehow wrapped in a transaction? Update Will probably do it because I take it that if the JSON is screwed for just one of the 120 values, you do want the other 119 done.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.Probably less than 0.5 seconds to run through that.Since you would need to insert all the data into a table before you ran the most efficient solution (single UPDATE statement with joined table), you need to wrap the inserts inside a single Begin Trans... If you want to compare performance against one of the recordset methods already mentioned, you should also use a transaction.somefield = somenewvalue .update is going to be sub-second performance on modern machines. Then it returns back to the above code to grad the next currency and repeats.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.

Leave a Reply