Today I feel obliged to link to the almost unknown utility, SQL Server Migration Assistant for MS Access.
This is a fairly helpful tool which can simplify migrating Schema and data between two otherwise fairly incompatible products.
What is interesting to watch is how the Assistant migrates queries to views and so forth.
It’s such a shame that replication to SQL Server CE couldn’t be more like this.
I’ve used this tool twice to migrate data from earlier (read:primitive) MS Access databases that I used to use for proof of concept projects.
A typical example is a database of our DVDs (>1,000 rows) and the SSMA does a great job in migrating the data. Although I’ve ojnly used it twice, I’d be interested to see whether it can do a better job of ETL (Extract/Transform/Load), because the Access DB grew to be denormilized over time and when porting to SQL it would be nice to try and normilize the data into a new schema.
Anyhow, give it a try if you need to migrate data from MS Access to SQL Server.
Remember: if you aren’t completely satisfied, you can always get your money back (by way I’m pointing out that it’s made available for $0).
