Migrating Access to SQL

I'm not familiar with the SQL Server Migration Assistant, but have some experience with the Upsizing Wizard.

The upsizing process is fairly easy and gives very good results. One gotcha I noticed on the data side is that all of the Access text fields were "upsized" to SQL Server text columns. I spent an extra day going through the resulting SQL Server database and setting up appropriately sized varchar() specifications on most of these columns so I could use string functions and operators, as well as apply indexes when needed.

Something else to consider are any non-table structures you may have in your access database.

If you have any forms, queries, macros or reports, you'll need to include your new SQL Server tables in the Access database as linked tables in order for these objects to continue to work.

If you want to include your Access queries within the SQL Server database, you'll need to rewrite them for SQL Server as views for select queries or stored procedures for update or insert queries. This is not always straightforward, as the syntax is different between the two products (You may use IIF() in Access which you would need to translate to a CASE statement in SQL Server, for example).

Microsoft have an app for that, they call it their upsizing wizard, details are on technet

I have run through it previously to see what happens and its pretty straight forward.