Solved

SSMA does not migrate tables

Posted on 2014-10-09
10
1,117 Views
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
SSMA.jpg
0
Comment
Question by:chtullu135
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 65

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.
0
 
LVL 36

Expert Comment

by:PatHartman
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40373241
Ok,
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."
http://www.microsoft.com/en-us/download/confirmation.aspx?id=42656

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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 36

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:chtullu135
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.
MigrationErrorMessage.jpg
0
 
LVL 26

Expert Comment

by:Nick67
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.
http://blogs.msdn.com/b/ssma/archive/2011/03/06/access-to-sql-server-migration-understanding-data-type-conversions.aspx.
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)
0
 

Author Comment

by:chtullu135
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.
0
 

Accepted Solution

by:
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.
0
 
LVL 36

Expert Comment

by:PatHartman
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.
0
 

Author Closing Comment

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

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

730 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