Solved

Trying to upsize an Access 2013 database to MS Azure

Posted on 2015-01-25
12
81 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 35

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 35

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
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.

 

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 35

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
 
LVL 35

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 35

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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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