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
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.
(See System Requirements)
Please use the following steps to migrate from Microsoft Access:
Hope this helps.
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.
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.
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...
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.
check this link for best practices
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.