Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SSMA does not migrate tables

Posted on 2014-10-09
Medium Priority
Last Modified: 2014-10-22
I've been trying to use ssma to migrate an access database to sql server 2012, but the tables aren't being migrated.  I'm experimenting with the Northwinds sample 2007 database.  When I click on the database tables in the migration wizard as shown in the attached picture, no tables are displayed.  I don't understand why it is such a problem to migrate access tables from the sample northwoods database to sql server 2012
Question by:chtullu135
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
LVL 66

Expert Comment

by:Jim Horn
ID: 40372912
<Very wild guess>
There may be a property for a Workgroup/Security file, and if your Access app isn't secured you still need to provide the 'unsecured' setup.mdw that shipped with Access.

This was the case with Speed Ferret (aka global find-and-replace tool), and some other third-party app I forget.

Good luck.
LVL 39

Expert Comment

ID: 40372958
I haven't looked at the A2007 version of the database but the tables might contain columns with the three data types that were added to make it easier to work with SharePoint but which are not standard and cannot be upsized to ANY real relational database.

Add a new table to the database with a couple of text fields and an autonumber primary key.  Do you see the new table when you run SSMA?
LVL 26

Expert Comment

ID: 40373241
Dumb question.
You do have the ability (proper rights, security, roles and access) to create a table on your SQL Server, right?
I mean, after all, if YOU can't create a table in the context you are logged in as, neither will SSMA.

Second dumb question.
SSMA installed correctly and without any errors, right?
In your image, it's throwing a 'class not registered' error.  That strikes me as a flawed installation type error.
From MS site, you will require the following to be installed:  "•Microsoft SQL Server Native Client (SNAC) version 10.5 and above. You can install SNAC from Microsoft SQL Server web site as part of SQL Server Feature Pack."

Third dumb question
You have SQL Server Management Studio installed, right?
So have you been able to create a test database with SSMS and add some dummy tables to it?
Have you been able to create linked files to that dummy database in Access?
Have you created a very simple Access database with a single table containing a single numeric field and tried to move it using SSMA?

In order for SSMA to work, your ducks have to be in a row with regard to access of the SQL Server, and that SSMA installed correctly.  Then you can start testing with a simple mdb to see if it is working, Northwind for a more complex example, and finally your production app.  Though I used it quite some time ago, at the time, SSMA wasn't smart enough to know a FE/BE Access setup and migrate the back-ends tables.  That made things more challenging.  Perhaps things have changed, though.
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

LVL 39

Expert Comment

ID: 40373280
You can also try the built in upsizing wizard and see what it does.  It will show you a list of tables and you choose the ones you want to transfer.  It should not show you tables with abomination data types since they can't be converted but it will show the others.  You don't actually have to go through with the upsizing.  All you want is to see the list of valid tables.

Author Comment

ID: 40373320
I tried the upsizing wizard and received the attached error message.  When I opened up the application via Access 2010, I was then able to migrate the tables to Sql Server.  From what I've read, there seemed to be an issue using the access 2007 migration wizard to migrate tables from a 32 bit Access application to a 64 bit Access application.  Apparently this issue was resolved in Access 2010.  I was able to migrate the Northwind table very easily via Access 2010.
LVL 26

Expert Comment

ID: 40373336
Abomination data types attachment fields and multi-valued fields.  Hyperlink fields are also not much fun.  They get moved but mangled. Details here.
Other fun things to pull the pin on are:
The use of -1 as TRUE in queries and code.  SQL Server uses 1 and 0 in bit fields.
The need to go into EVERY code statement of the type Set rs = db.OpenRecordset(SomeString, dbOpenDynaset) and add dbSeeChanges like so Set rs = db.OpenRecordset(SomeString, dbOpenDynaset, dbSeeChanges)

Author Comment

ID: 40373341
I was able to migrate a simple access 2007 database consisting of one table with two fields using the SSMA tool.  I will try the Northwinds database again.

Accepted Solution

chtullu135 earned 0 total points
ID: 40373445
Well now the SSMA works.  I was able to migrate the sample Northwind database tables to the SQL Server.  When I went to the SSMA folder, I notices that there were two migration assistants.  One was for 32 bit and the other was for, I assume, 64 bit.  When I used the 32 bit migration assistant, the migration using the SSMA tool went smoothly.
LVL 39

Expert Comment

ID: 40373595
Sorry I didn't mention the bit business earlier.  I too had significant problems when I first installed SSMA but I was getting errors so since you were not reporting errors, I didn't connect the dots.

When you install SSMA, it installs both versions but assumes you want the 64-bit version so that is what you see in the program groups.  You have to dig to find the 32-bit version.  The version you need to use depends on the bit-wise version of Access you want to convert from.  Since in most cases, you have 32-bit Access installed, that is the SSMA version you need.  All very unclear from the setup.

Author Closing Comment

ID: 40396436
I determined that I was not using the correct version of ssma

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question