I don't have any answer for a do-it-all migration tool for Access to SQL Server, but having done a few migrations (Access -> SQL Server, SQL Server -> Oracle) I can say that there is no fool proof panacea migration tool out there. You simply have to lay out your options, and pick the best possible path.

For Access to SQL Server, I would just lay out the tables manually in SQL Server, and then use SQL Server's DTS (Data Transformation Services) to migrate the data to the newly created tables.

For the purposes of the migration, you may need to drop all FK constraints and re-add them after the data has been migrated, to avoid as many headaches as possible.


HTH,
-Jason