Updating a form in ms access datingsights org
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?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.Another way would be to loop through my JSON file and pass each currency code & rate to a function that uses an SQL Query Qef to first locate the matching currency code and then update its rate.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?I quote myself, because i haven't had any problems with my Ge Force 8500 GT and HP w2207h LCD screen - anymore, until now.
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.
Not very efficient but maybe it doesn't matter in the grand scheme of things.
I would appreciate your opinions on what the best practice would be and 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.
If you mean create a temporary table, update from that then then delete the temporary table What I typically do is NOT drop the table, but batch empty the data from it, and fill it For example Dim db As Database Set db = Current Db C Do Cmd. Execute "delete * from temp Billing Clone", db Fail On Error 'fill it with records from selected jobid Do Cmd. It's a different loop, but still a loop And so, you do the loop-within-the- loop dim db as database dim rs as recordset set db = currentdb Dim key As Variant For Each key In po Dict. Openrecordset("Select Currency Code, Currency Rate from some Table where Some Field = " & Cstr(Key), dbopendynaset) 'is there more than one record To update? Currency Rate = po Dict(key) rs.update rs.movenext loop rs.close set rs = nothing Next There is only one rate per currency code.
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.