Exporting Access Tables to SQL Server Issues

When attempting to export a Microsoft Access table to SQL Server using the “Export to ODBC Database” functionality, the export can fail with the rather confusing message:

 “The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables”

This happened to us and on investigation we found that it was caused by invalid dates in one or more of the tables Date/Time type fields.  A quick way of finding them is to open the table and Sort A to Z each date column in turn.  The “bad” dates usually appear at the top of the sorted dates (sometimes shown as a time).  Correct or remove these dates and try again.  It worked for us!

Applies to: Microsoft Access 2007