Solved Update Query vs RecordSet FindFirst (1 Viewer)

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
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:

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
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
so would this 1 line be the equivalent of the 3 lines:


Code:
dataBase.Execute "UPDATE [FACT-DeliveryProducts] SET [del_prod_claim_cust] = " & CDbl(txtTotalQualityAmount) * percentageOfAmount & _
                    ", [del_prod_claim_cust_quant] = " & CDbl(txtTotalQuantityAmount) * percentageOfAmount & _
                    ", [del_prod_claim_cust_price] = " & CDbl(txtTotalPriceAmount) * percentageOfAmount & _
                    " WHERE [del_prod_id] = " & CLng(delProductID), dbFailOnError
 

cheekybuddha

AWF VIP
Local time
Today, 18:13
Joined
Jul 21, 2014
Messages
824
>> so would this 1 line be the equivalent of the 3 lines: <<
Yes.

Where is variable percentageOfAmount declared and set?
 

cheekybuddha

AWF VIP
Local time
Today, 18:13
Joined
Jul 21, 2014
Messages
824
BTW, when you use the query method there is no need to open the recordset (or even declare a recordset variable)
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
@cheekybuddha the percentageOfAmount is calculated earlier in the VBA code based on a number of factors including user input. the recordset definition was a copy paste thing where i just removed the findfirst and replace with the first queries and then the single query

if the single query does the job then i'm happy that i've (whilst continuing my reading and researching) figured out what i wanted to do. i guess this thread is more a "can someone check my work" to give me confidence
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
@Minty thank you i'll take a look at the link as well as everytime i get a bit more information it helps me go back through my "poor" decisions on how to do things (due to lack of knowledge and experience) and re-do in a more efficient way

so just for a final confirmation the update query and RBAR method both do the same thing but the update query is the "right" way
 

cheekybuddha

AWF VIP
Local time
Today, 18:13
Joined
Jul 21, 2014
Messages
824
i guess this thread is more a "can someone check my work" to give me confidence
(y)

You probably don't need to cast explicitly your inputs - they will be auto-cast into a string anyway (you are building a string, after all)
Code:
Dim dataBase As DAO.Database, strSQL As String

strSQL = "UPDATE [FACT-DeliveryProducts] " & _
         "SET [del_prod_claim_cust] = " & Me.txtTotalQualityAmount * percentageOfAmount & _
         ", [del_prod_claim_cust_quant] = " & Me.txtTotalQuantityAmount * percentageOfAmount & _
         ", [del_prod_claim_cust_price] = " & Me.txtTotalPriceAmount * percentageOfAmount & _
         " WHERE [del_prod_id] = " & delProductID
       
dataBase.Execute strSQL, dbFailOnError
Set dataBase = Nothing
If you use Me. you will get Intellisense to assist in writing your control names.

It's good practice to set any object variables that you have set to Nothing too.

hth,

d
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
@cheekybuddha thank you, appreciate the response. i do - as a matter of course - set things to nothing and close off recordsets etc so that is being done later on in the vba after the snippet above that i'm looking to replace the findfirst with the query

the Me. bit i seem to have lost the me's from the control names (not sure but probably me messing about ended up removing them)

the casting to CDbl i'll remove as i was thinking i'd need to make sure that the calculation part was using the right type

once again thank you
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Sep 12, 2006
Messages
14,689
I was once told "the answer is a query"
Generally it's more efficient to let the database engine do the hard work.

Also, A query is probably more easy to use with amendable parameters, and therefore be re-usable.
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
its one big ongoing learning process for me over the past 6 months from knowing nothing about databases to where i am today. my initial methods of doing things was very much a "traverse the data programmatically and do things as i go" the RBAR method described above, i'm finding now how to do replace those things with queries and move to a better, smoother, more efficient way of doing things

and everyone on this forum is so helpful and knowledgeable that its been a really straightforward learning process, i know what i want to achieve, i kind of know how to do it in a rudimentary fashion once i get it working i know i can count on this forum to help me move to the more efficient way of doing things
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Sep 12, 2006
Messages
14,689
@LGDGlen

They call it a learning curve, but it's not a curve. I think it's ascending a staircase, and you need help to get up each step.

Books are often no help. It's little tricks that other people show you that you would never ever work out from a book, but once shown you never forget. I think that's why most of us are mainly self taught. It's hard to teach advanced Access (or Excel, come to that). You just need thousands of hours of practice, and wise guidance here and there.
 

LGDGlen

Member
Local time
Today, 18:13
Joined
Jun 29, 2021
Messages
158
@gemma-the-husky your sig talks about scales and thanks buttons, i can't see any of them......... i've liked all the responses but i'm not sure if thats the right way to reward people
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:13
Joined
Sep 12, 2006
Messages
14,689
@gemma-the-husky your sig talks about scales and thanks buttons, i can't see any of them......... i've liked all the responses but i'm not sure if thats the right way to reward people
Thanks for pointing that out. It was an old feature, that has indeed been replaced by the like button. I had better amend my signature.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 13:13
Joined
Apr 27, 2015
Messages
4,355
Generally it's more efficient to let the database engine do the hard work.
Amen to that. As Pat Hartman is fond of saying, the "Access Way is the BEST way": ♪♫ I fought the law (Access) and the law (Access) won...♫♪
 

Users who are viewing this thread

Top Bottom