Speed of Code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve (sschmideler_at_perfect-product.com)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 12:49:48 -0600

I have an acess database and Table1 has 1800 records. Table2 has 14,000
records. What I need to do is look up an item in Table1 view its
corresponding entries in Table2 and pull a couple of fields from Table2 into
Table1 where those ITEMS are equal and the date is of the latest date.

How I do this now is open table 1 as a recordset, open table 2 as a
recordset. Start with the first item in table1 and cycle through match items
in table2 using a DO LOOP and where the date is the latest date I grab my
fields. When I reach the end of Table2 I use a DO LOOP to start table 1 at
record 2 and start over.

This process take 15 minutes to run through. Is there another way I can do
this without such a time penalty?

Relevant Pages

  • Re: Insert/Update table based on another
    ... table2 with table1 data ... You have already decided on a loop; ... That is a classic 1950's tape merge program done in SQL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Recordsetclone mistake
    ... > This continious form is linked with Table1 to be able to ... > Row Source is Table2. ... So the form knows about them, but its recordset ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.formscoding)
  • Re: About NextRecordSet
    ... Table1:= asp.NextRecordSet; ... Table2:= asp.NextRecordSet; ... > method my current recordset was changed. ... > iDummy: Integer; ...
    (borland.public.delphi.database.ado)
  • Re: Is there a solution to this?
    ... > Lets say I have two tables, table1 and table2 that have a one to one ... > The resulting recordset can be modified but if new records are added to ... then it gives a write conflict. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Deleting a datarow from a dataset!
    ... enumeration while enumerating through them. ... To get around this, use a for loop, and use the indexer on the ... table2, the program throws an exception saying the collection is ... How can I delete a row in table2 based on values from table1? ...
    (microsoft.public.dotnet.languages.csharp)