Faster and efficient way to find value in recordset then update?

[vman21]

Hi guys. I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.

What I did was:
1. Read tbCOMPANY each row using for loop
2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT
3. If tbCOMPANY.noMatch, it will insert into tbRESULT
4. else it will update the matched row in the tbRESULT

Its been taking like over 30 minutes to process this and still ongoing :banghead: my hair will turn white and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this ?

Thanks!

 

[spikepl]

Put an index on the two columns in which you search for matching values. If you don't know how then google.

 

[jdraw]

I agree with spike ---Indexing is the first place to look.
What is the database design (tables and relationships)?

 

[Galaxiom]

Any particular reason why you are using a recordset?

You could simply check for an existing record using a DLookup or DCount then inesrt the new record with a query.

It you really do want to use a recordset you would be better off creating the recordset using the two values as criteria in the recordset's query to just return any matching rows. If you get no rows then add the new record to that recordset.

Create a new recordset for each new test. It is much much faster to get a new recordset than to trawl through one holding all the records.

Also, even if you were working with the single recordset you would not go through it row by row but use Filter or FindFirst. Creating a new recordset for each check is still faster.