MS Access Application - Convert data storage from Access to SQL Server

Question

Bear in mind here, I am not an Access guru. I am proficient with SQL Server and .Net framework. Here is my situation:

A very large MS Access 2007 application was built for my company by a contractor.

The application has been split into two tiers BY ACCESS; there is a front end portion that holds all of the Ms Access forms, and then on the back end part, which are access tables, queries, etc., that is stored on a computer on the network.

Well, of course, there is a need to convert the data storage portion to SQL Server 2005 while keeping all of these GUI forms which were built in Ms Access. This is where I come in.

I have read a little, and have found that you can link the forms or maybe even the access tables to SQL Server tables, but I am still very unsure on what exactly can be done and how to do it.

Has anyone done this? Please comment on any capabilities, limitations, considerations about such an undertaking. Thanks!

Answer 1

Do not use the upsizing wizard from Access:

• First, it won't work with SQL Server 2008.

• Second, there is a much better tool for the job:
  SSMA, the SQL Server Migration Assistant for Access which is provided for free by Microsoft.

It will do a lot for you:

• move your data from Access to SQL Server
• automatically link the tables back into Access
• give you lots of information about potential issues due to differences in the two databases
• keeps track of the changes so you can keep the two synchronised over time until your migration is complete.

I wrote a blog entry about it recently.