none
Migration Assistant Access to SQL Server problems

    Question

  • I would like to migrate an Access 2003 .mdb to SQL Server 2008 R2 using the migration assistant.

    I did the following in the 'Microsoft SQL Server Migration Assistant Wizard for Access':

    Selected Migrate to SQL Server 2008 (there is no option to select SQL Server 2008 R2), accepted the default location. I then added the database. In the next page, I ticked all the boxes.

    If I drag over the .mdb, I get this message (see below):

    I then created a new database, ticked link tables so that it links to the current .mdb as a front end and then got this error msg:

    and then got msg's that the operation failed.

    I am not sure what to do next. I don't know if installing this will make a difference as I missed it out of the procedure: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10910 or if this is another issue.

     
    • Edited by meridius10 Monday, March 05, 2012 5:40 PM
    Monday, March 05, 2012 5:36 PM

All replies

  •  

    Hello,

    Make sure you have installed the DAO provider required by SSMA.
    SSMA v5.2 for example requires DAO provider version 12.0 or 14.0. You can install DAO provider from Microsoft Office 2010/2007 product or download it from Microsoft web site.

    http://www.microsoft.com/download/en/details.aspx?id=28763 (See System Requirements)

    Please use the following steps to migrate from Microsoft Access:

    http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, March 06, 2012 11:08 AM
    Moderator
  • Thanks. I can't seem to find the download link for 'DAO provider version 12.0' on the Microsoft site - any chance of sending me the link? 

    I am on Office 2003 and don't have 2007 or 2010 installed.

    Tuesday, March 06, 2012 1:28 PM
  • Hi meridius,

    We require the DAO to move data from the *.mdb files. SSMA 5.0 for Access requires either DAO provider version 12.0 or 14.0. The same can be installed from the Primary Interop Assemblies (PIA)redistributable http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18346.
     
    Most customers were under the impression that they need the full office suite to install this. That’s not correct we just need the Microsoft Office Access 2007 Runtime to install the PIA. The same can be downloaded from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4438.
     
    Once these are installed you should be able to overcome the DAO related exception.

    For more information, please refer to http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/08/06/ssis-execute-process-task-calling-a-remote-batch-file.aspx.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Wednesday, March 07, 2012 8:01 AM
    Moderator
  • I installed both of the above i.e.

    - Microsoft Access Runtime (English) 2007 12.0.6425.1000

    - Microsoft Office 2007 Primary Interop Assemblies 12.0.4518.1014

    On the user form when I tried to change a variable I got this msg:

    This has also been the case in a user form in a seperate database which is not linked to SQL Server at all so my guess is that the updates above have changed the way in which Access works.

    I also noticed that I cannot alter the date from the user form or the back end linked table in Access, however, this can be altered in the main table in SQL Server.

    The migration assistant only works using the Microsoft SQL Server Migration Assistant for Access (32-bit) version. The 64-bit version does not work but I am guessing that this has something to do with Office 2003 being a 32-bit disk.

    So this isn't quite as simple as I thought and there is some more work to do on my part...

    Thursday, March 08, 2012 9:45 AM
  • Update:

    The above procedure didn't work for me and created more problems so I used the upsizing wizard in Access 2003:

    Tools > Database Utilities > Upsizing Wizard

    Upsize all > 'Link SQL Server tables to existing application'

    In relation to the front end you will be left with a local table and a linked ODBC table. I am not sure if the local tables can eventually be deleted.

    This solution isn't perfect but causes less problems than the migration assistant.

    There is another option to 'Create a new Access client/server application'. This is a little more work and I can't remember how I modified links in the Access user form for the objects that were not picking up certain entities.

    Sunday, March 11, 2012 1:31 PM
  • When I get the time, yes!

    In the meantime the Upsizing Wizard in Access 2003 is far easier for now!...

    Friday, March 23, 2012 10:49 AM
  • do you get any solution for the same.
    Tuesday, May 01, 2012 9:45 AM
  • For Access 2003 the upsizing wizard is a better choice based on my experiences with the Migration Wizard.

    My experimentation with this is ongoing as you will see from this post: http://www.utteraccess.com/forum/Professional-Migrate-S-t1986088.html&gopid=2236185#entry2236185

    I would copy your Access db and trying to use the upsizing wizard using different combinations before making a decision.

    If you have Access 2007/2010 the Migration Wizard may work better then mine did with 2003.

    Tuesday, May 01, 2012 8:24 PM