Solved

Trying to upsize an Access 2013 database to MS Azure

Posted on 2015-01-25
12
73 Views
Last Modified: 2015-04-01
I have a MS account.  And I have setup an Azure SQL database.  But I cannot migrate my tables from a test database to Azure.  I'm using Microsoft SQL Server Migration Assistant for Access.

1. When I get to the screen "Create New Project" I choose SQL Azure.
2. Then I add the database from my computer.
3. Then when I get to the screen "Select Objects to Migrate I check the tables checkbox, although non of them display I still check it anyway.
4. Then when I get to the screen "Connect to SQL Azure I enter the server name (just the 1st part because the .database.windows.net is there already.
5. I enter my username and password and click Next.
6. Then I get to a screen where I can check "Link Tables" and click Next
7. And I get a conversion error:  "There is nothing to process."

I don't get it.
0
Comment
Question by:SteveL13
  • 6
  • 6
12 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
When you install SSMA, two versions actually get installed.  A 64-bit version and a 32-bit version and the install puts the 64-bit version on your desktop.  You probably need to be using the 32-bit version.  Bitwise - we are talking about the version of Access NOT the physical size of the hardware registers or the version of Windows you have running.  The install doesn't ask you, it simply makes an assumption.

Using the Start menu or Windows Explorer, locate the 32-bit version and make a shortcut for that. And place that one on your desk top.

Also, when using SSMA, you might want to set a different default for the datetime data type.  This won't matter if you are using the SQL server Native client version 10.0 or newer but if you are using "SQL Server" which may be the default, it will matter.  Change to DateTime which is the format that the older driver will recognize.
0
 

Author Comment

by:SteveL13
Comment Utility
I am using the 32 bit version of Access 2013.

Also, I'm using SQL Azure not SQL Server.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
OK but what version does SSMA think you are using?  Are you using the 32 bit version of SSMA? or the 64-bit version as I suspect?

You still use the SQL Server Native client to access Azure since Azure IS SQL Server.
0
 

Author Comment

by:SteveL13
Comment Utility
I am using the 32 bit version of Access 2013 but still can't get this to work.  Microsoft support has been terrible.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
WHAT VERSION OF SSMA ARE YOU USING????   I'll remind you again, the 64-bit version is installed as the default so unless you figured this out and changed it - the version of SSMA you are using WILL NOT work with your version of Access.


The 32-bit version works with the 32-bit version of office.  The 64-bit version works with the 64-bit version of Office.
0
 

Author Comment

by:SteveL13
Comment Utility
I am using the 32 bit version of SSMA after having tried the 64 bit version.  Neither work.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Are you sure that you tables don't contain any "abomination" data types.  Add a new table to the database you are trying to upsize.  Use only two columns a number and a text.  Define the number as the PK.  Make sure the column names don't violate any SQL standards.  Does that table show up?  If so, then the "abomination" data types are your issue.
0
 

Author Comment

by:SteveL13
Comment Utility
WOW!  That worked.  I create a new d/b with just one table designed as you suggested.  It worked!  Now why isn't me "real" d/b working?  Here is a snapshot of the table design:

table design
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
Look at the specific definition for each column.  Any of these data types will prevent the tables from upsizing.

1. If any have a Lookup defined, they cannot be upsized and so SSMA will not "see" the table.
Lookup2. If long text/memo fields are set to Append only.
Append3. Hyperlink
Hyperlink4. Attachment
Attachment
0
 

Author Comment

by:SteveL13
Comment Utility
Very strange.  Like I said, the test db I created upsized just fine.  But the "real" one I'm trying to upsize has nothing I can find that would/should prevent it from happening.  I looked over the data types and didn't find anything that would prevent it from working.

I've attached a sample db with the real data stripped out.
Sample-DB-for-Azure-Upsizing.accdb
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't have Azure installed so I can't try it for you.

Do you have SQL Server installed?  Can you try to upsize to that?
0
 

Author Comment

by:SteveL13
Comment Utility
I do not have SQL Server installed
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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