Forum Discussion
SQL Server Migration Assistant for Access - Hangs when selecting Access DB
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.
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?
- I think we've both confirmed that you need to install the correct bitness of SSMA for it to work!
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 ...
- 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 - 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. - 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.... 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...- 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 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.....- 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 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).
