Post reply

DB transfer from MS-access to SQL Server 2005

  • May 19, 2008 at 5:30 am

    #154362

    I am having a project to transfer a huge MS-Access database (with lot of relations and constraints :hehe:) to SQL server 2005. Currently we plan to retain the frontend in MS-access and later to some web application. Please suggest your better way to accomplish this. If you have any good link please provide me.

    Thanks in advance.

  • May 21, 2008 at 1:34 pm

    #818838

    If someone else has a more automated way to do this, I hope they will post a response for you. Otherwise, I would take the following steps:

    1. Create the empty database.

    2. Use the Import wizard in SSMS to import the data from the Access database. You should be able to select multiple source/destination tables (at least you can from a SQL Server source). When you get to the window where you can edit your table/field mappings, be sure to check the box to "enable identity insert" for any tables with identity columns (autonumber in Access) you may have. I would also verify the create table statement is what you are expecting. If you have any changes you would like to make, this is the place to do it. When you run the package, it will create the tables and copy the data for you.

    3. Create your indexes and constraints.

  • May 21, 2008 at 1:45 pm

    #818847

    Or - you could skip SSIS, and use a customized tool to do just what you're after: the SQL Server Migration Assistant for Access. It should transfer quite a bit (a lot more than just tables: views, constraints, etc...).

    More info over here:

    http://www.microsoft.com/sql/solutions/migration/access/default.mspx

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 23, 2008 at 4:51 am

    #819587

    I agree with Matt.

    Trying to customise stuff when all you are attempting to do is move from Access to SQL sounds like wayyyy too much work to me.

    The migration assistant is free, and requires the minimal intervention.

    Just make sure you back up the original version first...

    ~PD

Viewing 4 posts - 1 through 3 (of 3 total)

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