Forum Discussion

ChuckDonner11's avatar
ChuckDonner11
Copper Contributor
Jul 08, 2021

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

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 d...
Access to MS SQL Migration
isladogs's avatar
isladogs
MVP
Jul 12, 2021
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?

tgeary's avatar
tgeary
Copper Contributor
Jul 12, 2021
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.
  • isladogs's avatar
    isladogs
    MVP
    Jul 13, 2021

    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?

     

    • tgeary's avatar
      tgeary
      Copper Contributor
      Jul 13, 2021

      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).

      • isladogs's avatar
        isladogs
        MVP
        Jul 13, 2021
        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

Resources