Hi Guys

Here's the deal

Table 1 has 67,500 records in it, its an access DB. I have indexed the main field but not made it primary as it may have duplicate entries. I have made it duplicate entries okay.

Table 2 is a linked table , it is linked to a text file and is updated every two hours. This table has around 500 records in it. I am trying to do this: I now want to insert table 2 into table 1 but first I want to check through the table 2 records and make sure that the record doesn't already exist in table 1 which holds the 67,000 records.

so I have coded it to take the first record of the table 2 of which there are 500. And compare the order number to the order number of table 1. This means that I am looping through 67,000 records 500 times. It is taking quite literally hours to do this and is not practical.

Am I going about this the wrong way ! I need some help as I am new to this database programming lark in VB.

This is part of the code I am using ( the looping part)



While Not rstable2.EOF
While Not rstable1.EOF
If rstable1!orderNumber = rstable2!orderNumber Then
List1.AddItem rsreeldata!Number
End If
rstable1.MoveNext
Wend
rsreeldata.MoveFirst
rstable2.MoveNext

Wend


Many Thanx
Locutus