Maybe could just set a link to the Excel/CSV instead of using temp table. If sheet structure always same and file always saved with same name, link would persist. Either use the linked 'table' same as a local table (except cannot edit data) or then INSERT SELECT records to local table.
It is possible to connect to Excel/CSV and import directly, example for Excel:
Code:
Sub getExcelData()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
'set connection to worksheet and open recordset
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\June\Condos.xlsx" _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
rs.Open "SELECT * FROM [Sheet1$A1:C10]", cn, adOpenDynamic, adLockOptimistic
Debug.Print rs!Unit
'use DAO SQL to import to table
CurrentDb.Execute "INSERT INTO [tablename] SELECT * FROM [Excel 12.0 Xml;HDR = Yes;Database=C:\Users\June\Condos.xlsx].[Sheet1$]"
End Sub
"Best" method can be influenced by size of Excel/CSV file. Since you indicate the data is cumulative, it could get quite unwieldy over time.