Go to Top

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 Alex Mielus

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

4 Responses to "Using the new SSMA version 7.8, pitfalls to avoid"

  • Klaus Oberdalhoff
    July 3, 2018 - 3:41 pm Reply

    Hi,

    as i once wrote in my German / english blog
    http://www.insidesql.org/blogs/klausobd/2017/10/08/sql-server-migration-assistant-fuer

    If downloading either

    https://www.microsoft.com/en-us/download/details.aspx?id=13255 – Microsoft Access Database Engine 2010 Redistributable or
    https://www.microsoft.com/en-us/download/details.aspx?id=54920 – 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 - 5:18 pm Reply

      Thank you for adding value to this post Klaus 😉

  • George Hepworth
    July 3, 2018 - 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 - 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 Reply

Your email address will not be published. Required fields are marked *

 

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