My name is Angshuman Nayak and I am a part of SQL Developer support in Microsoft and work mostly around connectivity and data access technologies. As one of my activities I am helping customers and partners to move from various data bases like Oracle, Sybase, MySQL and Access to SQL Server. Today I am going to blog about a common scenario where customers and partners are stuck in their attempt to migrate data from Access.
While trying to migrate from Access using SSMA5.0 on a Windows 2003 SP2 / Windows XP SP2 you may stumble upon the following error.
"Error occurred while loading content. Could not load file or assembly 'Microsoft Office.Interop.Access.Dao, Version=220.127.116.11, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
We require the DAO (used for legacy purposes only, in case of you customer development avoid using it since it’s deprecated) 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. Wish you all the best for your data migration and welcome to the world of SQL Server.
Author : Angshuman(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Brian(MSFT), SQL Escalation Services, Microsoft