Note: My Access tables are Linked to SQL Server 2008 except for a few local temp tables. Local table naming convention is [~ImportTable]
Some of the threads on this were old.
Each Wall Street trading day, I receive an Excel worksheet with thirty thousand rows of future trading data for a commodity (puts and calls). The data layout (headers, title information...) is the same, however the number of records may change.

To automate this (i.e. let some other person click a button) the following programming steps are used:

1. Create a local Access table that is a clone of the production data table (in my case, a linked table to SQL Server 2008) i.e ~ImportOptions and ImportOptions
2. Use the code to delete all data in ~ImportOptions
3. Import all data from Excel into the local table
4. Use code to delete all header or random data in the ~ImportOptions table
5. Use SQL update query to convert any data in ~ImportOptions table
6. Append ~ImportOptions table into Production ImportOptions table

By staging each Excel sheet in a temp table, all data Quality Assurance or conversions can be done with SQL statements before appending the data.
Code:
' use a common dialogue to let user choose fileneme from directory and save it in variable strFileNameOption (teaser code uses custom dtIce format)
70        strFileNameOption = strICEPath & "\icecleared_gasoptions_" & Format(dtIce, "yyyy_mm_dd") & ".xls"
              '                      ---------- Main process -------------
420               ' Clean out the local staging table
430               CurrentDb.Execute "DELETE * FROM [~ImportOptions]"
                    Debug.Print "records in ~ImportOptions " & CurrentDb.TableDefs("~ImportOptions").RecordCount 
' Record count should display zero in debug window
440               DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "~ImportOptions", strFileNameOption, True, "ImportOptions"
Debug.Print "records in ~ImportOptions " & CurrentDb.TableDefs("~ImportOptions").RecordCount 
' Record count should display max number of records (header and any commend rows)

' Bonus Code - for all records in the local table row named "PRODUCT", delete all records that don't contain this string, that means its just random comments or empty rows between the data

450               CurrentDb.Execute "DELETE [~ImportOptions].PRODUCT, * FROM [~ImportOptions] WHERE ((([~ImportOptions].PRODUCT)<>'NG Fin'));"
                    Debug.Print "records in ~ImportOptions " & CurrentDb.TableDefs("~ImportOptions").RecordCount
                  '            CurrentDb.Execute "UpdateBasis", dbSeeChanges
                 
 ' delete rows with new 'F'  Column Contract has P, C, or F - Delete the rows that contain 'F'
465               CurrentDb.Execute "DELETE [~ImportOptions].[CONTRACT TYPE], * FROM [~ImportOptions] WHERE ((([~ImportOptions].[CONTRACT TYPE])='F'));"
                    Debug.Print "records in ~ImportOptions " & CurrentDb.TableDefs("~ImportOptions").RecordCount 
' The record count will be smaller now that the rows with 'F' were removed

' execute a Append Query (not shown)

470           CurrentDb.Execute "qryICEAppendOptions", dbSeeChanges
 
520           MsgBox "ICE Import to Database complete.", vbInformation, "ICE Daily Download"