SQL Server Migration Assistant for Access - Hangs when selecting Access DB

[ChuckDonner11]

I downloaded Microsoft SQL Server Migration Assistant for Access last week and successfully converted my local Access database to an online MS SQL database. After testing it for a week I decided to do it again with the current (updated with most recent data) version of the Access database. When I opened SMAA it asked to update. In addition, a Windows 10 update was installed on my PC a few days ago. Now the SMAA software hangs when I try to select my local Access Database. It shows "Connecting to database 'mydata'... but just hangs forever. This worked fine last week and this step did not delay at all. the migration took a while but not this step. Any help would be appreciated.

[#2]

I also had no issue with the tool prior to 7/9--I'm now getting the same behavior of hanging at 'Connecting to database'. I rolled back the SSMA update from 8.21 to 8.20, but the issue persists. I noticed Office 365 received an update on 7/9 (Access 16.0.14131.20278), so I believe that's the culprit. . Very frustrating.

[#3]

Easy to test whether the Office update is the cause. Rollback to an earlier version of 365

[#4]

For detailed instructions on how to rollback to an earlier version of 365, see https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a91... 

[#5]

@isladogs Thanks, but unfortunately I'm on a work PC where Office updates are managed company-wide, so I'm unable to test myself.

[#6]

OK that's a pity.
In that case. how were you able to get SSMA rolled back to 8.20?

I have exactly the same version of Office as you and am now downloading v8.21 of the Migration Assistant so I can try to replicate your issue. Are you able to upload a copy of the database you were trying to use with SSMA so I can use that in testing?
Have you tried all the usual steps - compile, compact, decompile, import to new blank database?

[#7]

I have some freedom with small utilities, less so with Office 🙂

I can't share this particular database, but I just re-created the issue with a blank, single-table test database. Issue persisted with both .mdb and .accdb.

[#8]

@tgeary 

Hi

I normally use the SQL Server Import / Export Wizard suppled with SSMS
I've just tested SSMA v8.21 having not used it for some time.

No problem with it hanging but its not seeing any tables in any of the databases I've tried.

I'm obviously doing something wrong but what?

[#9]

@isladogs 

Here are my steps for recreating this issue:

• Office 365 built 2106
• SSMA 8.20 or 8.21
• Create new database (TestDatabase.accdb) with a single table (TestTable)
• Launch SSMA, exit out of wizard if prompted
• Create new project
• Click "Add Database" and select TestDatabase.accdb
• Expand Access-metadata>Databases>TestDatabase
• Program stays on "Connecting to database 'TestDatabase'... indefinitely

It looks like you're not facing this issue. Do you mind sharing what version/build of Office you're on? I had no issues prior to 2106.

Concerning the 0 tables: have you tried right clicking on one database (e.g. GeoLocation) and selecting 'Refresh from Database'?

I eventually ended up using the SSMS import wizard to import my tables and then a bunch of T-SQL to fix the schema. Previously, SSMA handled both in one step without having to know any T-SQL (which was a lifesaver for a novice like me).

[#10]

Hi
Oops I has installed the 64-bit version of SSMA in error. It installed fine as I have 64-bit Windows
After replacing with the 32-bit version needed for 32-bit Access, it loaded tables and queries perfectly. I had no issues with it hanging.
As previously mentioned I'm testing with SSMA v8.21 and Office 365 version 2106 build 14191.20278

[#11]

@isladogs 

I went through the same mess; Microsoft's installer package for SSMA isn't "smart" enough to sense if you're installing the correct package (32- or 64-bit) so I accidentally installed the 64-bit version and it wouldn't work for me. I uninstalled it and then installed the 32-bit version and that one worked. Why can't the installer tell which one is the right one? Friggin' Microsoft.....

[#12]

I understand why you would think that but, in my case where I have 64-bit Windows, I could have installed either bitness of Office....or changed my mind and swopped bitnesses at a later point.

However, I don't see why the Office version can't be detected by the SSMA installer.
When I distribute Access apps via the Internet, I include both 32-bit & 64-bit versions of the ACCDE FE in the installer package. Script is used to determine which version of Office is installed and only the matching ACCDE is installed. To be absolutely accurate, to simplify script coding, both ACCDEs are installed and the 'wrong version' is then automatically deleted

[#13]

@isladogs I don't even know that you can install a 32-bit version on Windows anymore!

Where I work we use 32-bit Office (I don't know why the corporation hasn't switched to 64-bit). And I bet there are tons of big corporations that have enterprise-wide policies for using X version of Office, and none of the users or developers have anything to do with that, nor do they have any say in it.

Microsoft should have recognized that by now, they've had around 40 something years to figure it out...

[#14]

Sorry I'm confused by your first sentence.
You can certainly still install 32-bit Windows, Office & SSMA

There are many good reasons for running 64-bit Windows.
However, there are very few advantages in running 64-bit Office but many issues with doing so e.g. API declarations need converting. some reference libraries & ActiveX controls don't work....

[#15]

OK, I didn't know they even still made a 32-bit version of Windows. Must be for special situations.

I know there are a few considerations with 64-bit Office and you have to make some changes, like adding PtrSafe to API declarations and so on because Microsoft didn't properly deprecate their code, but it can give you more room for memory and larger capacity for data, which should be a good thing to make faster and more robust applications.

You know down the road at some point they're going to phase out 32-bit support overall. May as well be ready.

[#16]

Windows is still available in 32-bit and AFAIK is still widely used though gradually declining in favour of 64-bit which has better memory management etc. Its certainly not for 'special situations'.

Using 64-bit Office does not allow a larger capacity for data with the exception of Excel. However it could be argued that any Excel file that is too large for 32-bit should probably be a database in Access anyway.
The ONLY advantage of 64-bit Access that
I have ever noticed is large address awareness which can allow certain lengthy operations to be performed faster. However LAA is also coming to 32-bit A365 in a few months time!
So I certainly wouldn't be strongly encouraging anyone to convert to 64-bit Office until a time that suits them.

However, as a commercial Access developer, I've been using 64-bit and 32-bit Office for about 9 years and all my commercial apps are designed to work in both. I agree that eventually MS will probably phase out 32-bit software but don't expect that will be for several more years yet

[#17]

@isladogs 

I have Access 64-bit installed, but I had the same issue.

I tried using the 32-bit version of SSMA as you described but it didn't work either. It didn't hang but didn't find any tables either.

But then after uninstalling the 32-bit and re-installing the 64-bit SSMA, now it works fine??

Go figure ...

[#18]

I think we've both confirmed that you need to install the correct bitness of SSMA for it to work!

[#19]

This thread is not the newest anymore. However, I had the same issue after upgrading to SSMAA 8.25. Everything worked the day before and after the upgrade the same symptoms appeared.

My configuration WIN 10 64-bit; Office 365 64-bit. I work in an enterprise environment, so any rollback of O365 is out of discussion.

The final solution for me was to
1. uninstall SSMAA 64-bit version
2. uninstall Microsoft Access Database Engine 2016 Redistributable 64-bit version
3. reboot my machine
4. install SSMAA again 64-bit version
5. Install Microsoft Access Database Engine 2016 Redistributable 64-bit version

All was back to normal after this! Only removing/reinstallation of SSMAA didn't worked for me. Also other solutions with 32-bit and 64-bit that are mentioned here didn't worked.

Hope someone might find this helpful.