locked
DAO for SSMA 7.4 RRS feed

  • Question

  • I am trying to use SSMA to upsize some Access databases (2010 and 2016) to SQL Server (2016).

    I am running Windows 10 with Office 2016 (Office Proplus 365 locally installed).

    SSMA requires DAO 12 or DAO 14 to access Access databases.

    During installation it provides a download link to the Access 32-bit and 64-bit runtime installers to enable installation and registration of the DAO libraries. However, the installers conflict with the Click-to-run components and do not allow the installation to complete. (I have uninstalled the 64-bit Click-to-run registration component, which overcomes one issue with the 32-bit installer, but it simply fails later when it detects a Click-to-run installation).

    I have also tried MDAC to get the DAO libraries, but that won't install on Windows 10, (in compatibility mode or raw).

    I can bypass the DAO Download button in the SSM 7.4 installer, in which case I can install SSMA and it will run and connect to SQL Server and allow creation of a migration project etc. However, when it tries to extract the metadata for an Access (mdb or accdb) database, it fails with a missing DAO interop assembly error.

    Can anyone tell me how I can download the dll's for DAO 12 or 14 and install and register them so that I can get SSMA to work.

    (I have raised a Microsoft support ticket for this, but just end up being referred to Windows Server support, which would take me round the same loop again.)


    Paddy Boocock

    • Moved by Chenchen Li Wednesday, July 12, 2017 2:29 AM SSMA
    Tuesday, July 11, 2017 10:01 AM

Answers

All replies

  • Are you referring to the Microsoft Access Database Engine?

    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    I'm afraid I've never heard of DAO versions 12.0 or 14.0 so I am assuming they are referring to the ACE OLEDB Provider. MDAC is for ADO and it's installed with the OS.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 11, 2017 12:29 PM
  • There are “several” challenges with SSMA 7.4

    First up, it is a x64 bit only version. Prior versions installed “two” versions during the install (x32 and x64). Since in the past the “vast” majority of office installs were x32, then you simply launched the x32 bit version of SSMA.

    However, 7.4 is x64 only. And “more” of a challenge this means you have to have an x64 bit version of ACE (the new JET engine and DAO replacement) installed on your computer. And worse yet is it tends to be difficult to install x32 and x64 versions of ACE on a given computer. (as you pointed out).

    I should point out that the DAO libraries are and have been replaced by ACE. They are compatible, but for a considerable amount of time Access does not use nor require a reference to DAO. Since 2007, you set a reference to ACE, and this is “how” you gain use of DAO now. So for almost 10 years now, you do NOT use nor set a reference to DAO in Access anymore – you use ACE.

    You best bet then is to install an x64 bit version of ACE. (SSMA 7.4 ONLY has an x64 bit version and will ONLY work with x64).

    And as noted, if you have existing versions of office, then conflicts can and will OFTEN exist.

    I would “try” installing the Microsoft Access Database Engine 2010 Redistributable – x64 bit version.

    https://www.microsoft.com/en-ca/download/details.aspx?id=13255

    From above select AccessDatabaseEngine_x64.exe

    Because this is a 2010 version of ACE, then it has a “high” chance of not conflicting with your office install.

    So while DAO has been depreciated and not receiving updates, the new ACE which replaces DAO is most certainly being updated, enhanced and is 100% compatible with DAO.

    Regards,
    Albert D. Kallal (Access MVP, 2003 - June 2017)
    Edmonton, Alberta Canada

    Tuesday, July 11, 2017 7:17 PM
  • Hello Paddy,

    Thanks for posting here. According to your description, your issue about SSMA is out of scope, so I would move this thread into SQL Server Migration forum. Hope you could find solution there. Thanks for your understanding.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 12, 2017 2:29 AM
  • SSMA requires DAO 12 or DAO 14 to access Access databases.

    Hello,

    DAO is already integrated in Windows XP and higher Version.

    No, what you are looking for is the InterOp for DAO (and Office), it's mandatory for SSMA for MS Access; you can get it here:   Microsoft Office 2010: Primary Interop Assemblies Redistributable


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Paddy Boocock Saturday, July 15, 2017 10:44 AM
    Wednesday, July 12, 2017 5:41 AM
  • Thank you

    Paddy Boocock

    Saturday, July 15, 2017 10:44 AM
  • Hi,

    Was the info published in our blog not helpful?

    https://blogs.msdn.microsoft.com/datamigration/2016/12/16/access-connectivity-components-for-ssma/

    Can you please let us know what is not accurate there/didn't work for you, so we can update it accordingly?

    Thanks,
    Alex.

    Monday, July 24, 2017 4:02 PM
  • I installed the Microsoft Office 2010 Interop Assembly as suggested above - this did not resolve the problem and SSMA still failed to access the Access metadata.

    I then tried installing the Access 2016 Runtime from the link above, using /QUIET to bypass the error message about 64-bit. 

    This did not work, so I then installed the Access 2016 64-Bit Runtime, again using /QUIET to bypass the error message about 32-bit.

    SSMA now works correctly and I can convert an MDB file to a SQL Server database.

    However, I am now no longer able to run Access on the machine to make the necessary changes to the front end:

    When I try to run Access, I get the following error displayed in a message box:

     "Automatic configuration of the current version of Microsoft Access has failed. This can occur if you do not have the necessary permissions to install Microsoft Access on this computer."

    Is there a way to resolve this without defeating the operation of SSMA?


    Paddy Boocock

    Friday, August 18, 2017 10:24 AM
  • Hi,

    2017-10-07

    download not any longer possible - download page fails - not found (404)


    Klaus Oberdalhoff Germany

    Friday, October 6, 2017 11:04 PM
  • Hello Klaus,

    Which download do you mean? I tested ACE and PIA download links and both work.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, October 7, 2017 3:40 AM
  • Hello,

    I fought with all the possible drivers 32 or 64B, Server Provider Sql, Microsoft DAO provider 10, 12 or 14, runtime access 2016, 2013.

    SSMA 7.6 does still not work.


    only one solution works is to the use ssma 6.0 + runtime access2010

    it's possible to launch ssma 6.0 in 32b or 64b for DAO provider.

    I am French and like all French people I have trouble with English

    2.5.0.0
    Monday, November 27, 2017 4:18 PM
  • At least your troubles are "fair warning" Paddy.   I am going to give up before I start and just use another machine where I have a different copy of Office installed.    Thanks - and I really mean that.   Could have struggled for hours here and tried all the options you have been through with no success either. 
    Wednesday, January 17, 2018 7:20 PM