Migrating from Access to SQL 2008

  • Can someone explain the 3 different options in the upsizing wizard?

    1) Create a new Access client/server application

    2) Link SQL server tables to existing application

    3) No Application changes

    I want a brand new SQL database without any attachment to Access, which one do I choose?

    Should I use something other than the upsizing wizard?

  • Newbie Jones (5/9/2013)


    Can someone explain the 3 different options in the upsizing wizard?

    1) Create a new Access client/server application

    2) Link SQL server tables to existing application

    3) No Application changes

    I want a brand new SQL database without any attachment to Access, which one do I choose?

    Should I use something other than the upsizing wizard?

    I believe all of the upsizing options of Access maintain some sort of connection to Access. Please see this link if you haven't already:

    If you're comfortable with SQL Server and the SSMS ((SQL Server Management Server) tool, you could use import to get your data from the Access tables to SQL Server. This is for tables only. Do you have Access queries and reports that you want to migrate as well? As you may already know, T-SQL has a different syntax that Access SQL so adjustments need to be made.

  • Yes I have queries and reports as well that need to be migrated. Looks like that may be the most difficult part of the process.

  • Hello,

    Alternatively, try Access to MySQL Converter. It is able to migrate Access queries to MSSQL views automatically.

  • This has been done quite a few times at my company by people who prototype in Access and then move to SQL Server. The Data Import is the most reliable way I've personally seen of moving data, but the Queries (both SELECT and UPDATE) will be the more time-consuming part of the process.

    In the end, the performance gains you'll realize by moving the database will be worth the effort.

    If I may offer two pieces of advice:

    1. Script everything in the migration effort and test...test...test.

    2. When it comes time to release, take the application offline, run your scripts and bring the application back up pointed to the new data source. Have a fallback position so if something goes awry, you can fall back to using the Access database until you get it right. Ideally, you'll get it right the first time, but if not, you certainly won't be the first.

  • Take a look at the Microsoft free tool called "Microsoft SQL Server Migration Assistant. I used it for a year grooming my Access backend database for conversion to SQL Server. Using SSMA, you can repeatedly convert tables and queries to SS, review the results, test them, and then do it all over again after some changes. It was an invaluable tool few people know about.

    Here's the link:

    http://www.microsoft.com/en-us/download/details.aspx?id=28763

    Jim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply