SQL Server Migration Assistant Error

Question

I am trying to convert my access database to SQL Server 2008. I am running Access 2003 and am using the SQL Server Migration Assistant 2008. As soon as I try to import the current Access database I get the following error:

Access Object Collector error: Database
     Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass' to interface type 'Microsoft.Office.Interop.Access.Dao._DBEngine'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00000021-0000-0010-8000-00AA006D2EA4}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
     An error occurred while loading database content.

I am not really sure how to get past this. I found the DAO360.ddl file in program files/common files and in windows/servicepack files. I have reinstalled Access and also tried the other version of the migration assistant. At this point I am stuck. Any suggestions how to get beyond this barrier?

Thanks!

Answer 1

Hello,

Do you use compression on the hard disk drive where the DAO360.ddl is located? If that is the case disable compression and run the Assistant again.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Hi Alberto,

No compression on either part of the hard drive where the files are.

Thanks

Answer 3

Hello,

Please add the path of the DAO360.dll to the ‘PATH’ environment variable. The procedure will be the following:

1.    Right click properties on My Computer

2.    Go to the tab “Advanced”

3.    Click on the button “Environment Variables”

4.    On the System Variables section, look for the variable “Path” and click on it.

5.    Once highlighted, click the “Edit” button.

6.    Go to the end of the “Variable value” , add a semicolon and then add the path of the DAO360.dll.

7.    Click OK button

8.    Click OK button

9.    Click OK button
 

After that run the Assistant again.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

Thanks for taking the time to write your detailed reply. Unfortunately this is still a no go. Any other thoughts would be greatly appreciated. I didn't think I would be hung at this spot.

Answer 5

Hello,

Please register the required Dao360.dll:

1.    Click Start, and the click Run.

2.    Type the following command:

a.    Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\Dao360.dll"

Hope this works.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 6

That did the trick!! Thanks so very much. Now all I have to do is go through the giant list of errors. ;-)

Answer 7

Hello,

Thank you for the feedback.

Have fun!

Regards,

Alberto Morillo
SQLCoffee.com

Answer 8

Hello Alberto,

I'm having the same problem. I registered the Dao360.dll as you suggested in your previous post.
There were several entries in the registry for this file.
C:\Program Files\Common Files\Microsoft Shared\DAO\ is in the system path variable.
Vijay Patil from the SSMA Team has been looking at this issue for me and here is her last email.
I’m running XP PRO SP3, Access 2000 and SQL Server 2008 Enterprise Edition.

(From Vijay)

Hi Howard,
 
We have tried to migrate the .mdb which you have shared with Access 2000 and the migration was successful.
We are unable to replicate the same error at our end. There could be issue with the DAO file and so is the error which you have mentioned.
This could be one reason for the issue which you are facing.
 
Please check this below link for information on DAO
http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/65892da4-4c1e-4578-9667-22faf87f2ac8
 
Thanks,
SSMA Team

I’ve included the entries from the log for your review. Any and all suggestion would be greatly appreciated.
I will supply any additional information you may require. Thank you for your time with this matter.

Best Regards
Howard

[Gui: Info] [3172/1] [2010-03-30 15:17:58]: Starting user interface initialization.
[Gui: Info] [3172/1] [2010-03-30 15:17:59]: User interface initialization finished.
[Generic: Mandatory] [3172/1] [2010-03-30 15:17:59]: SQL Server Migration Assistant 2008 for Access v4.0.1336
[Gui: Info] [3172/1] [2010-03-30 15:18:02]: MainMenu: press button - text:File, name:File
[Gui: Info] [3172/1] [2010-03-30 15:18:09]: MainMenu: press button - text:New Project..., name:newProject
[Gui: Info] [3172/1] [2010-03-30 15:18:16]: MainMenu: press button - text:Tools, name:Tools
[Gui: Info] [3172/1] [2010-03-30 15:18:17]: MainMenu: press button - text:Global Settings, name:GlobalSettings
[Gui: Info] [3172/1] [2010-03-30 15:18:41]: MainMenu: press button - text:File, name:File
[Gui: Info] [3172/1] [2010-03-30 15:18:44]: MainMenu: press button - text:Add Databases, name:connectToSource
[Collector: Error] [3172/5] [2010-03-30 15:18:55]: Access Object Collector: An error occurred while loading database content.
[Collector: Error] [3172/5] [2010-03-30 15:18:55]: Exception: Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154.
 site: Microsoft.SSMA.Framework.Access.DaoUtils.DaoMdbConnection ConnectToDatabaseImpl(System.String, Boolean, System.String, System.Nullable`1[Microsoft.SSMA.Framework.Access.DaoUtils.WorkgroupPolicySettings])
 source: Microsoft.SSMA.Framework.Access.DaoUtils
 error code: -2147221164
   at Microsoft.SSMA.Framework.Access.DaoUtils.DaoUtilities.ConnectToDatabaseImpl(String fileName, Boolean readOnly, String password, Nullable`1 workgroupPolicySettings)
   at Microsoft.SSMA.Framework.Access.DaoUtils.DaoUtilities.TryConnectToDatabase(String fileName, Boolean readOnly, String& password, PasswordAsker passwordAsker, Nullable`1& workgroupPolicySettings, WorkgroupPolicyAsker workgroupPolicyAsker)
   at Microsoft.SSMA.Framework.Access.DaoUtils.DaoUtilities.ConnectToDatabase(String fileName, Boolean readOnly, String& password, PasswordAsker passwordAsker, Nullable`1& workgroupPolicySettings, WorkgroupPolicyAsker workgroupPolicyAsker)
   at Microsoft.SSMA.Framework.Access.Generic.AccessConnectionUtilities.ConnectToMdbFile(XNode xMdbFile, Boolean readOnly, IUIAskPasswordProvider askPasswordProvider, IUIWorkgroupPolicyProvider workgroupPolicyProvider)
   at Microsoft.SSMA.Framework.Access.Collector.Loaders.MDBFileLoader.LoadChildren(XNode xNode)
[Gui: Info] [3172/1] [2010-03-30 15:18:56]: Access Object Collector error: Database
     Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154.
     An error occurred while loading database content.

[Gui: Info] [3172/1] [2010-03-30 15:19:01]: MainMenu: press button - text:File, name:File
[Gui: Info] [3172/1] [2010-03-30 15:19:05]: MainMenu: press
 

Answer 9

Hello Howard,

I was not able to see your post until now.

I hope you have found a solution.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 10

Existe un fix en el siguiente enlace que arregla el problema:

http://support.microsoft.com/en-us/kb/2019235

Instala el fix para tu sistema y versión de office y se solucionará el asunto del registro.

Un saludo.

Angel Pérez

todoaccess.com