Using the new SSMA version 7.8, pitfalls to avoid

Microsoft has been updating their SQL Server Management Assistants on a regular basis, and they just updated the SSMA for Access. However you can’t see what’s new for 7.8 in their official documentation. The latest version of SQL Server Migration Assistant (SSMA) version 7.8 can be downloaded from here.

The 7.8 version is much easier than prior especially with handling 32/64 bits but there are quirks, which we’ll be looking at.

Which version should I download?

SSMA must be able to connect to Access and in order to do that, it must be the same number of bits as the installed Access. For that reason, if you have 32-bit Access, you should download and install the 32-bit SSMA. Note that 32-bit programs are also referred to as “x86”. Otherwise, you should install 64-bit SSMA to work with 64-bit Access.

SQL Server Migration Assistant v 7.8

Positive feedback

I loved the fact that SSMA recognized from the very beginning that the server was on Azure SQL. Big plus, thumbs up!

When using Office365 you need to download Access Database Engine 2010

Not long ago I needed to install it on a client’s VM and, while doing it, I came across these errors / bugs.

When running Office 365, you will need to download the Microsoft Access Database Engine 2010 Redistributable so that SSMA can read your Access data. The Microsoft Access that comes with Office365 is in a sandbox environment and therefor not accessible to SSMA.

Required component missing

Additional problems you may encounter with SSMA

After installing Microsoft Access Database Engine 2010 Redistributable I had another error, also related to Office 365. This thread may help!

Office 16 Click and Run

In order to resolve the issue I uninstalled Office 16 Click-To-Run Extensibility Component 64-bit Registration – see image below.

Uninstall Office 16 Click and Run

I couldn’t migrate all tables at the same time

After logging into SQL Server, I selected the tables which I wanted to synchronize and hit the Convert Load and Migrate button. The migration didn’t take place though for all tables, but only one! So I was only able to migrate one table at a time, which is terrible. Think about having to migrate 100+ tables and queries, that wasn’t my issue but still … a nightmare.

You will need to add foreign keys yourself

My local Access database didn’t have any foreign key constraints set up. When migrating to SQL, SSMA didn’t ask me to set foreign key constraints. Technically not an issue with SSMA tool itself, but something to be aware of and to check when migrating since, I guess, the original database didn’t have any constraints, so we need to make sure we enforce it. SSMA should do that for us.

What bugs or errors did you get when using SSMA? Where they crucial for your project? Let us know in the comments below.


About the Author:

Alex is a Web & Access Developer and also the web server admin at IT Impact.


  1. Klaus Oberdalhoff September 10, 2018 at 7:08 am - Reply


    the biggest problems with SSMA i have are the following:

    a) SSMA does not convert Unique keys allowing NULLs correctly.

    You have to delete the keys first and then add them manually
    as filtered Unique Index to sql Server like

    USE [YourDatabase]

    /****** Object: Index [idx_Kunde_Matchcode_notnull] Script Date: 09.04.2017 22:11:26 ******/
    CREATE UNIQUE NONCLUSTERED INDEX [idx_Artikel_Matchcode_notnull] ON [dbo].[tblStamm_Artikel]
    [a_Matchcode] ASC
    WHERE [a_Matchcode] IS NOT NULL;

    b) Depending on ODBC driver (if installed by IT) the standard convertion date to datetime2(n) does not work corrrectly.

    c) I want timestamp on each table.

    So you have to set at least the default values before converting to Date –> Datetime and timestamp always.

    mfg Klaus

  2. Klaus Oberdalhoff July 5, 2018 at 3:16 pm - Reply


    i’m using WIndows 10 64 bit – Access 13 – 32 bit – SQL Server (2 Versions Developer and Express) 2017 (64 bit) SSMA 7.8 and i migrate with the wizzard,
    Starting SSMA with wizzard – selecting mdb or accdb (tested both) – it automatically selects ALL tables and no queries …. so i easily can convert ALL tables AT ONCE ?

    So i really can’t understand your problem …

    mfg Klaus

    • Alex July 27, 2018 at 8:10 am - Reply

      I was using Windows 10 64 bit, Access 2016, 32 bit and SQL Server 2016 myself. On my end SSMA 7.8 did act like I described it at that particular time on that particular machine: I couldn’t migrate all tables in a single go. Why? I have no idea 🙂

      After that, when I used it, it DID convert all selected tables correctly.

  3. Klaus Oberdalhoff July 3, 2018 at 3:41 pm - Reply


    as i once wrote in my German / english blog

    If downloading either – Microsoft Access Database Engine 2010 Redistributable or – Microsoft Access Database Engine 2016 Redistributable

    and – now the part that is not written down –
    open cmd-line and install it with the runtime switch /quiet

    Access…._X64.exe /quiet

    then it WILL install the distributable without message and without complaint

    and you CAN afterwards install and use the 64-bit SSMA 7.6 without problem ….

    Just did it on my machine and it works flawlessly .

    mfg Klaus

    • Alex July 3, 2018 at 5:18 pm - Reply

      Thank you for adding value to this post Klaus 😉

  4. George Hepworth July 3, 2018 at 9:56 am - Reply

    I’m not sure I would even want SSMA to “guess” at foreign key restraints. Perhaps it could suggest those it thinks it has found, though.

    With regard to one-at-a-time migration, I believe the solution would be to manually select each table you want to migrate individually in the tree view.

    • Alex Mielus July 3, 2018 at 10:01 am - Reply

      Thank you George for your input. That’s correct, it would be a good thing to have SSMA show us what FK’s it finds, before actually setting them on SQL Server.

      the solution would be to manually select each table you want to migrate individually in the tree view

      That’s exactly the problem I described, not the solution. I was not able to migrate all tables in one go, but one at a time.

Leave A Comment


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.