Solved

Trying to upsize an Access 2013 database to MS Azure

Posted on 2015-01-25
12
75 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
ID: 40569673
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
ID: 40570643
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
ID: 40571193
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
ID: 40580627
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
ID: 40580767
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
ID: 40580889
I am using the 32 bit version of SSMA after having tried the 64 bit version.  Neither work.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 34

Expert Comment

by:PatHartman
ID: 40581652
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
ID: 40581703
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
ID: 40581765
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
ID: 40584795
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
ID: 40584847
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
ID: 40584913
I do not have SQL Server installed
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

929 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

14 Experts available now in Live!

Get 1:1 Help Now