Solved

SSMA does not migrate tables

Posted on 2014-10-09
10
850 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
Comment Utility
<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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
I determined that I was not using the correct version of ssma
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now