Hi All
I am very green when it comes to databases and queries etc so i'm constantly learning as i go. initially i had some processes which update records in a table using a recordset and findfirst function but the more i learn and understand the more i'm trying to improve the way in which my database works. what i need to understand is whether the following has the same outcome but is just a better and ultimately quicker way of doing things:
vs
i'm guessing that there is maybe a more efficient way of doing 1 update query and updating all 3 fields so if thats the case any suggestions on how to merge the 3 queries together would be helpful as well
thanks in advance
Glen
I am very green when it comes to databases and queries etc so i'm constantly learning as i go. initially i had some processes which update records in a table using a recordset and findfirst function but the more i learn and understand the more i'm trying to improve the way in which my database works. what i need to understand is whether the following has the same outcome but is just a better and ultimately quicker way of doing things:
Code:
Set dataBase = CurrentDb
Set delProdsRS = dataBase.OpenRecordset("FACT-DeliveryProducts", dbOpenDynaset)
delProdsRS.FindFirst "[del_prod_id]=" & CLng(delProductID)
If Not (delProdsRS.NoMatch) Then
delProdsRS.Edit
delProdsRS!del_prod_claim_cust = CDbl(txtTotalQualityAmount) * percentageOfAmount
delProdsRS!del_prod_claim_cust_quant = CDbl(txtTotalQuantityAmount) * percentageOfAmount
delProdsRS!del_prod_claim_cust_price = CDbl(txtTotalPriceAmount) * percentageOfAmount
delProdsRS.update
End If
vs
Code:
Set dataBase = CurrentDb
Set delProdsRS = dataBase.OpenRecordset("FACT-DeliveryProducts", dbOpenDynaset)
dataBase.Execute "UPDATE [FACT-DeliveryProducts] SET [del_prod_claim_cust] = " & CDbl(txtTotalQualityAmount) * percentageOfAmount & " WHERE [del_prod_id] = " & CLng(delProductID), dbFailOnError
dataBase.Execute "UPDATE [FACT-DeliveryProducts] SET [del_prod_claim_cust_quant] = " & CDbl(txtTotalQuantityAmount) * percentageOfAmount & " WHERE [del_prod_id] = " & CLng(delProductID), dbFailOnError
dataBase.Execute "UPDATE [FACT-DeliveryProducts] SET [del_prod_claim_cust_price] = " & CDbl(txtTotalPriceAmount) * percentageOfAmount & " WHERE [del_prod_id] = " & CLng(delProductID), dbFailOnError
i'm guessing that there is maybe a more efficient way of doing 1 update query and updating all 3 fields so if thats the case any suggestions on how to merge the 3 queries together would be helpful as well
thanks in advance
Glen