help ! Access problems need to search a lot of records

[locutus]

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

[Anders Englund]

I'm not sure, but it could be quicker to search the large table for the order no instead. And if they match, do whatever...

//Anders

[BruceG]

If I understand your problem correctly, I would do something like this (using SQL to search for a matching record is MUCH faster than looping thru the whole table):

Dim rsTemp As Recordset
'(your other Dims here)
Do Until rstable2.EOF
    Set rsTemp = dbMyDB.OpenRecordset _
         ("SELECT COUNT(*) As RecCount " _
          & "FROM Table1 WHERE OrderNumber = '" _
          & rsTable2!OrderNumber & "'")
     If rsTemp!RecCount = 0 Then 
         dbMyDB.Execute "INSERT INTO Table1 VALUES('" _
            & rsTable2!OrderNumber & "', '" _
            & rsTable2!OtherField & "', '" _
            & rsTable2!LastField & "')"
     End If
     rsTable2.MoveNext
Loop

[Edited by BruceG on 07-19-2000 at 01:29 PM]