Create a table to hold the import statistics:
tblStatistics
------------
StatisticID_PK - Autonumber
ImportFileName - Text
ImportTableName
ImportDate - Date/Time
BeforeImportRecCount - Long
AfterImportRecCount - Long
Then modify your code; you will have to insert the lines in the proper place:
WARNING: The following is Air code!
Code:
Dim d as DAO.Database
Dim BeforeImport as Long
Dim AfterImport as Long
DimRecDiff as Long
Dim sSQL as String
Set d = Currentdb
'First Import
BeforeImport = d.TableDefs!TableName1.RecordCount
Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName1, xlFileName1, HasFieldNames
AfterImport = d.TableDefs!TableName1.RecordCount
sSQL = "INSERT INTO tblStatistics (ImportFileName, ImportTableName, ImportDate, BeforeImportRecCount, AfterImportRecCount)"
sSQL = sSQL & " VALUES ( 'xlFileName1', ' TableName1', #" & Date() & "#, " & BeforeImport & ", " & AfterImport & ");"
d.Excute sSQL, dbfailonerror
'second Import
BeforeImport = 0
AfterImport = 0
BeforeImport = d.TableDefs!TableName2.RecordCount
Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName2, xlFileName2, HasFieldNames
AfterImport = d.TableDefs!TableName2.RecordCount
sSQL = "INSERT INTO tblStatistics (ImportFileName, ImportTableName, ImportDate, BeforeImportRecCount, AfterImportRecCount)"
sSQL = sSQL & " VALUES ( 'xlFileName2', ' TableName2', #" & Date() & "#, " & BeforeImport & ", " & AfterImport & ");"
d.Excute sSQL, dbfailonerror
'Third Import
'as above - and repeat 7 more times
'clean up
set d = Nothing
Create a query on table tblStatistics. In the query do the subtraction to get the number of records imported.