I'm trying to import data from Excel spreadsheets into my database. The spreadsheet column headings don't match the table fields, and in some cases contain characters Access doesn't like. My plan is to import the spreadsheets into a temporary table, then append them to the real table.



The problem happens when I try to import second identical spreadsheet.

Here's what happens:

  • Import Data1 into a new table.
  • Invalid column headings--Access assigns automatically
  • Import successful


  • Import Data2, appending records to imported Data1 table
  • Invalid column headings--Access assigns automatically (same names as for the previous import)
  • Error:


  • Then: "An error occurred trying to import the file.....The file was not imported."

This doesn't make sense because the fields should be renamed.
Any thoughts?