My previous posts about the SQL Migration
Assistant for Access reported generally favorable results with this product
which is used to migrate Access data into SQL Server.
It does a remarkably better job than the old Access Upsizing Wizard which very
often failed, especially when dealing with date fields. When SSMA has completed
its work, the system is mostly operational.
However, there are two common Access design flaws that can impact the
application - the first is relying on the behavior of Boolean fields to
evaluate as -1 = True and 0 = False. Many Access developers will test for
"-1" meaning True, but SQL Server returns "1" for bit
fields so the comparison logic fails. The fix is simply to substitute
"-1" with "True" (without the quotes) and the operation
will succeed.
A second design flaw recently noted is that in Access you can subtract one date
from another date, and then perform math on the result - for example
24 * ([date_end] - [date_start])
in Access will give you the number of hours between the two date/time values.
However, once your data is moved into SQL Server, this expression will result
in
Implicit conversion from data type smalldatetime to int is not
allowed. Use the CONVERT function to run this query.
The solution is to use the DateDiff function which exists in both Access and
SQL Server. In Access you would write
DateDiff("h", [date_start], [date_end])
and when converting this query to T-SQL for SQL Server you might use
DATEDIFF(hh, [date_start], [date_end])