Moving from Microsoft Access 2007 to Sql Server 2005

Question

I have MS Access 2007 Databases and VBA modules associated with it.

I Now want to upgrade my project so as to use Sql Server 2005 and Vb.net using visual studio.net.

Can any one suggest Complete details on the transfer without much hassle or data loss.?

Thanks In Advance

Answer 1

Start with the Ms-Access SSMA site: http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx

I am not aware of a migration guide specifically for Access -> SqlSever, however, there is a very extensive Oracle -> SqlServer Migration Guide that you could easily cherry-pick for a project plan.

Answer 2

Per RBarry's answer...the data migration to SQL Server is easy using the SQL Server Migration wizard. Then you will have a working microsoft access application (probably but maybe it will require polishing and tweaking).

To convert the front end (ui) to vb.net there might be a converter but even if there is one you would likely have to do a lot of it manually anyway. So you treat the access version like a prototype and you start building your app in vb.net by hand.

Seth

Answer 3

I use SQL Server to import data from Access databases. Be aware that it won't import your keys or relationships- you'll need to recreate those.

Your VBA code is specific to Access. You'll need to rewrite it in VB.NET.

The Access queries are probably going to cause you the most trouble. Simple ones can be converted with the Upsizing Wizard, but the more complex ones will have to be rewritten in SQL.

Also note SQL Server objects follow different naming conventions. An Access table is typically prefixed with tbl, SQL tables typically aren't named with a prefix.