Solved

Trying to upsize an Access 2013 database to MS Azure

Posted on 2015-01-25
12
86 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 37

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 37

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 37

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 37

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 37

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 37

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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