-
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
-
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
-
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):
Code:
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]
