Pitfalls to Avoid When Using the New Microsoft SSMA Version 7.8
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.
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.
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!
In order to resolve the issue I uninstalled Office 16 Click-To-Run Extensibility Component 64-bit Registration – see image below.
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 
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.
Hi,
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]
GO
/****** 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;
GO
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
Hi,
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
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.
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
Thank you for adding value to this post Klaus 😉
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.
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.
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.