locked
SSMA Not Working for me - Access 2016 to SQL Server Azure RRS feed

  • Question

  • I have a very simple 1 table Access db that I would like to migrate to SQL Azure to show feasibility. I downloaded the SSMA. I start the Wizard.

    1. I can Add the local Access database.

    2. I can Connect to the SQL Azure instance.

    3. Then when creating the project it fails in Loading the db.

    Any ideas?

    Thanks,

    Shawn

    Thursday, July 7, 2016 10:08 PM

Answers

All replies

  • Here is the log output:

    [Gui: Info] [12708/1] [2016-07-07 15:36:25]: ToolBar: press button - text:Migration Wizard, name:runWizard
    [Collector: Error] [12708/20] [2016-07-07 15:36:53]: Access Object Collector: Error occurred while loading children.
    [Collector: Error] [12708/20] [2016-07-07 15:36:53]: Exception: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site: Void LoadChildren(Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.Framework.Access.Generic
       at Microsoft.SSMA.Framework.Access.Collector.Loaders.MDBFileLoader.LoadChildren(XNode xNode)
       at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultLazyLoadProvider.LoadChildren(XNode node)
    [Collector: Error] [12708/20] [2016-07-07 15:36:53]: Inner Exception #1: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site:
     source:
    [Gui: Info] [12708/1] [2016-07-07 15:36:53]: Access Object Collector error: Database
         Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
         Error occurred while loading children.
    [Collector: Info] [12708/17] [2016-07-07 15:37:09]: Connection to SQL Azure established successfully.
    Connection string: Server=tcp:aebi.database.windows.net;Database=Aebi Library;User ID=aebism;Trusted_Connection=FALSE;Encrypt=TRUE;TrustServerCertificate=FALSE;
    [Gui: Info] [12708/1] [2016-07-07 15:37:09]: Connection to SQL Azure established successfully.
    Connection string: Server=tcp:aebi.database.windows.net;Database=Aebi Library;User ID=aebism;Trusted_Connection=FALSE;Encrypt=TRUE;TrustServerCertificate=FALSE;
    [Collector: Info] [12708/17] [2016-07-07 15:37:09]: Microsoft SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8
     Apr 27 2016 19:29:47
     Copyright (c) Microsoft Corporation
    [Gui: Info] [12708/1] [2016-07-07 15:37:15]: Starting conversion...
    [Gui: Info] [12708/1] [2016-07-07 15:37:15]: Analyzing metadata...
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Access Object Collector: Error occurred while loading content.
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Exception: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site: Void LoadContent(Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.Framework.Access.Generic
       at Microsoft.SSMA.Framework.Access.Collector.Loaders.MDBFileLoader.LoadContent(XNode xNode)
       at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultLazyLoadProvider.LoadContent(XNode node)
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Inner Exception #1: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site:
     source:
    [Gui: Info] [12708/1] [2016-07-07 15:37:15]: Access Object Collector error: Database
         Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
         Error occurred while loading content.
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Access Object Collector: Error occurred while loading children.
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Exception: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site: Void LoadChildren(Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.Framework.Access.Generic
       at Microsoft.SSMA.Framework.Access.Collector.Loaders.MDBFileLoader.LoadChildren(XNode xNode)
       at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultLazyLoadProvider.LoadChildren(XNode node)
    [Collector: Error] [12708/12] [2016-07-07 15:37:15]: Inner Exception #1: Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
     site:
     source:
    [Gui: Info] [12708/1] [2016-07-07 15:37:15]: Access Object Collector error: Database
         Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
         Error occurred while loading children.
    [Gui: Info] [12708/1] [2016-07-07 15:37:15]: Prerequisites failed.
    [Gui: Info] [12708/1] [2016-07-07 15:37:19]: Conversion finished with 0 errors, 0 warnings, and 0 informational messages.
    [Gui: Info] [12708/1] [2016-07-07 15:37:19]: Operation canceled.
    [Gui: Error] [12708/12] [2016-07-07 15:37:20]: LowLevelAdapter.ConvertTree : Convert error: Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverserException: Operation canceled.
       at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Generic.Factories.Utils.OpTreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Access.SqlServerTreeConverter.Access2SqlServerTreeConverter.TraverseTree(ITreeConversionContext context, TreeConversionState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Generic.TreeConverters.Default.DefaultTreeConverter.ConvertTree(XNode sourceNode, ITreeConversionContext context)
       at Microsoft.SSMA.GUI.Generic.LowLevel.AppCommandProcessing.Commands.SsmaAppConvertCommand.StartConvert(Object context).
    [Gui: Error] [12708/12] [2016-07-07 15:37:20]: Exception: Operation canceled.
     site: Void TraverseTree(TContext, TState, Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.Framework.Generic.Factories
       at Microsoft.SSMA.Framework.Generic.Factories.Utils.TreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Generic.Factories.Utils.OpTreeTraverser`2.TraverseTree(TContext context, TState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Access.SqlServerTreeConverter.Access2SqlServerTreeConverter.TraverseTree(ITreeConversionContext context, TreeConversionState state, XNode sourceNode)
       at Microsoft.SSMA.Framework.Generic.TreeConverters.Default.DefaultTreeConverter.ConvertTree(XNode sourceNode, ITreeConversionContext context)
       at Microsoft.SSMA.GUI.Generic.LowLevel.AppCommandProcessing.Commands.SsmaAppConvertCommand.StartConvert(Object context)
    [Gui: Info] [12708/1] [2016-07-07 15:37:24]: MainMenu: press button - text:&Tools, name:Tools
    [Gui: Info] [12708/1] [2016-07-07 15:37:27]: MainMenu: press button - text:&Show Log, name:ShowLog

    Thursday, July 7, 2016 10:38 PM
  • The Object Prerequisites Not Met on top of dialog box stands out. In the log it has a few statements like

    Gui: Info] [12708/1] [2016-07-07 15:37:15]: Access Object Collector error: Database
         Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

    I would expect Office 2016, Access, or the SSMA to include the requisite DAO libraries.

    I did install the SNAC from the SQL Server 2012 Feature Pack. No difference in behavior.

    Good grief. Such an easy task made so hard.

    Thursday, July 7, 2016 11:07 PM
  • Hi aebism,

    I’m sensing that the issue could be your Access database file is in a legacy format(.mdb) so it’s not supported by the DAO libraries(version 16) that comes with Office 2016. In this case, if your are using SSMA for Access 6.0 I would suggest you install Access Runtime 2010 see if the issue goes away.

    For more information, please review MSDN blog.

    If you have any other questions, please let me know.

    Regards,
    Lin




    Friday, July 8, 2016 9:57 AM
  • I suspect you are on the right track. The SSMA doesn't give any clues that .mdb file format is not supported - that would be nice information to have, yes? Or to put it another way, to migrate an Access 2016 database to SQL Azure you need to install the Access 2007 runtime component. Not exactly obvious.

    Now I have to figure out how to use the runtime. Going into Access Save As does not seem to offer any ideas. I'll keep at this - thanks for pointing me in the right direction.

    Sad that they didn't consider this scenario when building the SSMA.

    Friday, July 8, 2016 6:30 PM
  • A couple quick points.

    My Access db is in Access 2016 so it is stored as filetype .accdb

    I installed both the PIA and Access 2007 Runtime in the same folder as the Access db and it made no difference.

    So I suspect I need to do something other than just *install* these files.

    The MSDN blog doesn't really add any insight other than confirm your recommendations.

    Friday, July 8, 2016 9:17 PM
  • Hi Shawn,

    I did some additional test on newly deployed machines, here’s the result:

    Testing environment: VM1 with Windows 10 Enterprise, Office 2016 and SSMA for access 6.0 installed.
                                               VM2 with Windows 10 Enterprise with only SSMA for access 6.0 installed.

    I then create 3 different Access db files(.accdb 2007-2016 format, .mdb 2000 format and .mdb 2002-2003 format) for testing.

    On VM1, I was able to migrate all three access files to SQL azure without issue. So it appears I was wrong, DAO that comes with Office 2016 do support mdb file.

    On VM2, I noticed that during SSMA installation, it did ask me to install Access runtime as prerequisites. If I choose not to install access runtime, I got exact same error message in SSMA. After install access runtime, I was able to migrate all three access file to SQL Azure without issue.

    So it appears the issue could be with your Office 2016 installation, I would suggest you repair it first.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Saturday, July 9, 2016 6:58 AM