ACC97: Error Importing Excel Worksheet with Mismatched or No Column Headers into Existing Access Table (237996)

RESOLUTION

1. Count the number of columns (N) in the Excel worksheet. Make sure that the data that you want to import from the Excel worksheet is defined as a named range.
2. Create a new query in Access. Do not add any tables to the query.
3. On the Query menu, click SQL View.
4. Change the SQL statement to one of the following. Use the first example if you do not have column headings in the Excel worksheet. Use the second example if the column headings in the Excel worksheet do no match the field names in the Access table.

      INSERT INTO [<Dest. Table>] (<Table field names separated by commas>) 
      SELECT F1, F2, F3,...,FN
      FROM [EXCEL 8.0; HDR=NO; IMEX=2; 
      DATABASE=<Path to Excel file>;TABLE=<NamedRange>].<NamedRange>;
   					

   -or-

      INSERT INTO [<Dest. Table>] (<Table field names separated by commas>) 
      SELECT <Worksheet column names separated by commas>
      FROM [EXCEL 8.0; HDR=YES; IMEX=2; 
      DATABASE=<Path to Excel file>;TABLE=<NamedRange>].<NamedRange>;
   					

   NOTE: F1 to FN should continue out to the number of columns being imported. Also, the order of the fields in the destination table field list must match the order of the unnamed fields in Excel. Replace the text inside the angle brackets (<>) with information about your Excel worksheet and Access table. The "<" and the ">" should be removed as well.
   
   
5. Run the query. The data in the Excel worksheet should now be imported into the Access table.