More on the Weird VB code/Access 2000

[cafeenman]

OK, it works for my brother. But he said that the table he is updating from has almost 20,000 records and the code has been running for 15 minutes and is still going.

My question now (for all you SQL geniuses) is can all these queries and recordsets be combined into a single update query using sub-queries?

sSQL_NEW = "SELECT * FROM New_Table"
Set RST_NEW = CurrentDb.OpenRecordset(sSQL_NEW, dbOpenDynaset) ' works fine for me
 
sSQL_LG = "SELECT * FROM Big_Table ORDER BY ID ASC"
Set RST_LG = CurrentDb.OpenRecordset(sSQL_LG, dbOpenDynaset)
 
With RST_LG
  .MoveFirst
  Do Until .EOF
    sSQL_SM = "SELECT TOP 1 * FROM Small_Table WHERE ID < " & !ID & " ORDER BY ID DESC"
    Set RST_SM = CurrentDb.OpenRecordset(sSQL_SM, dbopensnapshot)
    RST_NEW.AddNew
       RST_NEW!ID = !ID
       RST_NEW!Loan = !Loan
       RST_NEW!Pmt = !Pmt
       RST_NEW!Inv = RST_SM!Inv
    RST_NEW.Update
    .MoveNext
  Loop
End With