I've got a client that wants to import some huge Excel spreadsheets into an Access database, but the column headers and column datatypes don't match the structure of my destination table.
My plan is to import the spreadsheet into a staging table in a temporary database, then use an Insert query to insert the data from that table into the destination table in the current database with a syntax similar to:
INSERT INTO tbl_FinalDest (field1, field2, field3)
SELECT iif(NZ([field1], "") = "", 0, cdbl([Field1]), Field2, Field3
FROM tbl_Staging IN 'C:\zz_Staging.accdb'
But I cannot seem to figure out the syntax to import the Excel spreadsheet into the staging database.