Outer & Inner Loop 1

[air1access]

I have a recordset I'm tryin' to update via a Loop...
The base tbl has duplicate values in one field called IID. The varying tbl has the IID only once...
Linked by IID between both recordsets - the code I'm working with runs thru & updates only the 1st IID in the base tbl, and then it moves to the next IID, updates the 1st IID & so on... Instead of updating the rest of the IIDs that are equal. I need for the code to loop thru the IIDs that are equal updating other fields values, and then move to the next IID in the recordset, etc..

I sure hope this makes sense...!! Any suqqestions or examples..?? Below is what I'm working with thus far...
Thanks in advance..
air1access

Do While Not rstVarying.EOF
rstBase.FindFirst "[IID] = '" & rstVarying!IID & "'"
If Not rstBase.NoMatch Then
rstBase.Edit
rstBase!PrimeMPN = rstVarying!PrimeMPN
rstBase!Description = rstVarying!Description
rstBase!Class = rstVarying!Class
rstBase!FleetType = rstVarying!FleetType
rstBase.Update
End If
rstVarying.MoveNext
Loop

 

[PHV]

Why not using an update query ?
A starting point (SQL code):
UPDATE tblBase INNER JOIN tblVarying ON tblBase.IID = tblVarying.IID
SET tblBase.PrimeMPN = tblVarying.PrimeMPN
, tblBase.Description = tblVarying.Description
, tblBase.Class = tblVarying.Class
, tblBase.FleetType = tblVarying.FleetType

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[air1access]

PHV,
I guess because the base tbl is over 500,000 records and I was thinkin' that some "DAO coding" and a loop will be a faster update then an Update Query...??
Is this a true statement..??
Plus the varying tbl is a Select Query - which I believe I can't update the base tbl from a Select Query..??? I would have to create another Append or Make Tbl Query, and then use that tbl as the varying recordset...
Is there a preferred method for updating a very large recordsets...??

air1access

 

[MajP]

No way can you loop faster than SQL. This would be orders of magnitude slower.

Use very good SQL practices. Ensure proper indexes, return only the fields and records you need, optimize the SQL, etc.

 

[JoeAtWork]

Looping is almost always slower than an SQL statement, usually much, much slower.

 

[air1access]

All,

Sweet...!! I really appreciate you all being a "myth buster" & educating me on this...!!!!

Is there a certain way I should set up my queries to run them...
Like - Docmd.RunSQL or db.Execute...?? Or even another way..??

Thanks again for the help.!!
air1access

 

[MajP]

docmd.runsql only works on an action query so if the string is an action query then you are ok. If you want to run an already stored query then
docmd.openquery works

You are always better off trying to run an already stored query then to write the query string in code. The reason has to do with the JET query optimizer. A stored query has already been optimized

http://support.microsoft.com/kb/209126

 

[air1access]

Majp,

Wow..!! I had it all wrong..!! This is why Tek-tips is the huckleberry...!!!

Thanks for this info - I am incorporating the changes right now..

Best Regards,
air1access

 

[MajP]

There was one time on TekTips where I was able to write code that was much faster looping than anyone could propose for a sql solution (someone probably could have come up with a good sql). The poster had about 750k records and for some reason wanted to compare all records and return any records that subtracted from another record would equal 0.
Most sql solutions required each record to look at every other record. These queries usually locked up the user db.
because you are doing 750K X (750K - 1) comparisons

I used a binary search algorithm that required only to make on the average 5 searches per record. So it was about 5 * (750/2) searches. I did this with arrays so it was far faster than recordsets. But that is one time in many posts.