SSMA does not migrate tables

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
Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
PatHartmanCommented:
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
Nick67Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
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
Juan VelasquezAuthor Commented:
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
Nick67Commented:
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
Juan VelasquezAuthor Commented:
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
Juan VelasquezAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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
Juan VelasquezAuthor Commented:
I determined that I was not using the correct version of ssma
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.