Query performance issues
Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " &amp; _
            "SELECT InputTable.ID, " &amp; _
            "       Last(InputTable.FID), " &amp; _
            "       Last(InputTable.KeyValue1), " &amp; _
            "       Last(InputTable.KeyValue2), " &amp; _
            "       Last(InputTable.Date1) " &amp; _
            "FROM " &amp; _
            "    (" &amp; _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " &amp; _
            "        FROM InputTable " &amp; _
            "        WHERE Date1 &lt;&gt; '' " &amp; _
            "        ORDER BY ID, Date2 " &amp; _
            "    ) " &amp; _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") &amp; " seconds"
    Set cdb = Nothing
End Sub


Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds