findfirst doesn't find added records

  • Thread starter Thread starter David F.
  • Start date Start date
D

David F.

Hello...
I've come across this in the past and now trying to do a simple export of
data to another db is doing it again. I read records from one table, do a
findfirst to see if the record already exists in the new table, if not, add
it. Problem is that the findfirst at some point in time doesn't find the
record that was already added whereas other times it finds records
correctly. I think it has something to do with dynaset, but what's up with
that????
 
David F. said:
Hello...
I've come across this in the past and now trying to do a simple export of
data to another db is doing it again. I read records from one table, do a
findfirst to see if the record already exists in the new table, if not,
add it. Problem is that the findfirst at some point in time doesn't find
the record that was already added whereas other times it finds records
correctly. I think it has something to do with dynaset, but what's up
with that????
Click to expand...


You might need to requery the recordset ...

rst.Requery

This is a potentially resource-heavy and time-consuming operation.

An easier and probably more efficient and reliable solution would be to
create a link to the target table, then you can use a simple append query to
append the new records ...

INSERT INTO TargetTable ( ID, Field1 )
SELECT SourceTable.ID, SourceTable.Field1
FROM SourceTable
WHERE (((SourceTable.ID) Not In (SELECT ID FROM TargetTable)));

It's possible to do the same thing without a linked table using the IN
keyword, but using a linked table is simpler.
 
Brendan Reynolds said:
You might need to requery the recordset ...

rst.Requery

This is a potentially resource-heavy and time-consuming operation.

An easier and probably more efficient and reliable solution would be to
create a link to the target table, then you can use a simple append query
to append the new records ...

INSERT INTO TargetTable ( ID, Field1 )
SELECT SourceTable.ID, SourceTable.Field1
FROM SourceTable
WHERE (((SourceTable.ID) Not In (SELECT ID FROM TargetTable)));

It's possible to do the same thing without a linked table using the IN
keyword, but using a linked table is simpler.
Click to expand...

Thanks, I found that last night and it seemed to help the second import I
needed to do. Only in that last case I had to split/extract data from
fields to the new DB. It took forever! Seek wasn't available; isn't there
something faster than the findfirst method? If I had to do it again, it may
have been faster to fire up the AS/400 and put the DB on there and write an
RPG program to process the data (if I remember how).
 
David F. said:
Thanks, I found that last night and it seemed to help the second import I
needed to do. Only in that last case I had to split/extract data from
fields to the new DB. It took forever! Seek wasn't available; isn't
there something faster than the findfirst method? If I had to do it
again, it may have been faster to fire up the AS/400 and put the DB on
there and write an RPG program to process the data (if I remember how).
Click to expand...

You could try using an outer join instead of the subquery to return only
records that don't already exist in the target table. That might be faster.

INSERT INTO TargetTable
SELECT SourceTable.*
FROM TargetTable RIGHT JOIN SourceTable ON TargetTable.ID = SourceTable.ID
WHERE (((TargetTable.ID) Is Null));
 
Back
Top