findfirst doesn't find added records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David F. - 23 May 2007 02:34 GMT

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????

Reply to this Message

Brendan Reynolds - 23 May 2007 10:54 GMT

> Hello...
> I've come across this in the past and now trying to do a simple export of
[quoted text clipped - 4 lines]
> correctly. I think it has something to do with dynaset, but what's up
> with that????

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.

Signature

Brendan Reynolds

Reply to this Message

David F. - 23 May 2007 19:32 GMT

>> Hello...
>> I've come across this in the past and now trying to do a simple export of
[quoted text clipped - 22 lines]
> It's possible to do the same thing without a linked table using the IN
> keyword, but using a linked table is simpler.

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).

Reply to this Message

Brendan Reynolds - 24 May 2007 10:52 GMT

>>> Hello...
>>> I've come across this in the past and now trying to do a simple export
[quoted text clipped - 29 lines]
> 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).

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));

Signature

Brendan Reynolds

Reply to this Message