Migrating accdb to sql server 2008r2

  • Not sure if this is the right place to post. I was looking for information in regards to migrating from access database accdb to sql server 2008r2. My plan would be to create the whole back-end by script. I've never done this before so any suggestions would be appreciated.

    Thanks

  • Check out the possibilities with SSIS.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • I was looking at it but haven't got a chance for a deeper dive. Will it offer me a completely scripting solution?

    Thanks

  • It will do much more than you think. Please refer SSIS in BOL for further detail.

    You can also think about database import which also support Access source.

    Refer following links which might be useful:

    http://technet.microsoft.com/en-us/library/ms140052.aspx

    http://www.sqlservercentral.com/Forums/Topic1139114-391-1.aspx

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Take a look at SSMA, it is not a scripting solution but makes it very easy to migrate:

    Microsoft SQL Server Migration Assistant for Access v5.2

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

    "SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft that simplifies database migration process from Access to SQL Server. SSMA for Access automates conversion of Microsoft Access database objects to SQL Server database objects, loads the objects into SQL Server, and then migrates data from Microsoft Access to SQL Server.

    SSMA for Access v5.2 is designed to support migration from Microsoft Access 97 and higher to all editions of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure."

    I have used this in the past to move Access into SQL and it works very well. In some cases you may find that you need to rename tables / columns if they violate MSSQL naming rules - if so then a great tool is Find & Replace from rickworld.com which costs about $40 USD.

  • Agree...Start with SSMA. I've used it in the past without any issues.

  • but remember that both solutions are only going to migrate the database and data, not any GUI. (please tell me that your Access application has a front-end database with the user controls and back-end database with the data..)

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

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