updating table based on date

[girky]

Hello,
I have 2 tables that I need to link based on dates and I'm not sure the most efficient way to do it. One table is contains:
Table1
Type Dateofoccurance
1 1/2/06
4 3/8/06
1 6/9/06

the other contains rates for each type and an effective date:
Table2
Type Rate EffectiveDate
1 50 1/1/06
1 100 5/1/06
4 25 1/1/06

Here's my code to find the appropriate rate based on the effectivedate:
--------------------

Public Function buildwork() As Boolean

Dim conn As New ADODB.Connection
Dim rsTable1 As New ADODB.Recordset, rsTable2 As New ADODB.Recordset, rsTable3 As New ADODB.Recordset
Dim sSql As String, sRate As Single

Set conn = CurrentProject.Connection
conn.Execute "delete * from Table1"
rsTable1.Open "qryTable1", conn, adOpenKeyset, adLockReadOnly, adCmdStoredProc
rsTable3.Open "Table3", conn, adOpenKeyset, adLockOptimistic, adCmdTable

rsTable1.MoveFirst
Do Until rsTable1.EOF
If rsTable1![Dateimported] >= Date - 6 Then
        sSql = "Select qryTable2.* from qryTable2" & _
               " where qryTable2.[type]='" & Replace(rsTable1!type, "'", "''") & "'" & _
               " order by [effectivedate] desc;"
        rsTable2.Open sSql, conn, adOpenKeyset, adLockReadOnly
        
        If rsTable2.EOF Then
            sRate = 0
        Else
            rsTable2.MoveFirst
            If rsTable1!Date >= rsTable2!effectiveDate Then
                sRate = rsTable2!AcceptableRate
            Else
                rsTable2.MoveNext
                If rsTable1!Date >= rsTable2!effectiveDate Then
                    sRate = rsTable2!AcceptableRate
                Else
                    rsTable2.MoveNext
                    If rsTable1!Date >= rsTable2!effectiveDate Then
                        sRate = rsTable2!AcceptableRate
                    Else
                        rsTable2.MoveNext
                        sRate = rsTable2!AcceptableRate
                    End If
                End If
            End If
        End If
        rsTable2.Close  
    rsTable3.AddNew
    GoSub FILL_CalculatedVolumes
    rsTable3.Update
End If
    rsTable1.MoveNext
Loop

buildwork = True

done_buildwork:
    rsTable1.Close
    rsTable3.Close
    Set conn = Nothing
    Exit Function
    
FILL_CalculatedVolumes:
With rsTable3
    !employee = rsTable1!employee
    !type = rsTable1!type
    !Date = rsTable1!Date
    !AcceptableRate = sRate
End With
Return
End Function

-----------------------------

This gives me:
Table3
Type Rate DateofOccurance
1 50 1/2/06
1 100 6/9/06
4 25 3/8/06

My problem is that Table1 is very large and this takes a long time to run. Is there a better/more efficient way?
Thanks!

 

[kjv1611]

Have you tried using SQL instead of a database. With really large tables, it's best to use SQL (queries), as it's much more efficient in most, if not all, cases.