Resolved: SSMA unable to convert Date/time in MS Access to Datetime in SQL Server

Question:

I’m migrating my Access backend to SQL Server using SSMA. The SSMA is throwing an error when making changes in the mappings. I have the Date/Time field in Access which needs to be DateTime in SQL to work. Now SSMA by default sets mappings of date to datetime2 which when run migrates successfully to SQL but is shown as a short text field in Access. When I try to manually update the mappings in SSMA from datetime2 to DateTime and start the migration I receive the following error(Please find the details of the error through the link below):
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Table in Access:
The table in SQL when migrated from Access with datetime2
SSMA Error details when migrating with destination mapping as DateTime instead of datetime2

Answer:

I would consider running a query on the access (client side), and looking for some bad dates in that column.
Null dates should work just fine. It is possbile that the access column is in fact NOT a datetime column?
And yes, it is MUCH preferred to setup to use datetime in SQL server as opposed to datetime2.
However, you CAN use datetime2 in access as linked tables, and it WILL see this correctly as a datetime (and not text). But for this to work, you MUST then install + use + link your tables using the newer ODBC drivers. (native 11 or later). The 2nd fallout of this choice is that then any and all workstations that plan to use Access with these linked tables ALSO must have that SAME native 11 (or 17 or 18) installed. So, you have to re-link your tables using the newer driver, and you also have to ensure that each workstation also has that newer ODBC driver downloaded and installed on each workstation.
Above will thus allow access to see/use the datetime2 columns correctly as date time in access and VBA code.
however, as a general rule, it sounds like you have some bad dates in your original data. I would create a query that looks for say a date less then 1960, or some such, and see if any records are returned. If such older dates are not valid, then update then with a query to null values.
Sounds to me, that there exists some bad date values in that data. You could ALSO migrate, then change the datetime2 back to datetime on the server side, but with lots of tables, and lots of date columns, that could be quite a bit of work.
Since this looks to be ONLY one table, then as noted, try a query on that table in Access that looks for dates older then what your data supposed to have. Clean those out, and migrate again.

If you have better answer, please add a comment about this, thank you!