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=126.96.36.199, 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
HoneyHirting Smart Recruiting for jobs management Track multiple jobs.Track jobs from scheduling to hiring.Shortlist Candidates and Schedule interviews.Maintain historical data of jobs.Recruiters management Tracks all jobs Tracks Company accounts Tracks candidates history Tracks candidates from short listing to interviews to hiring. Candidate management Tracks multiple candidates Mass upload of candidate data. Maintain historical data of candidate Company management Tracks all jobs of a company Tracks all interviews of jobs Tracks company job and hiring history Copyright © 2011 HoneyEaters Software Pvt Limited Solutions. All Rights Reserved.
Is the Access 2007/2010 runtime a requirement? I already have a full version of Access 2002 and I don't want to have to deal with the issues of running multiple Access versions on one machine. I tried installing just the Primary Interop Assemblies, but that did not do it. I also tried installing the MS Access Database Engine 2010 Redistributable (www.microsoft.com/.../details.aspx) but that did not help either.